sql

select SUBSTR(‘20140901‘, 1, 6) AS CALC_MONTH, --统计时间
       (select ACCURATE_START_DATE from (SELECT  EMPLOYEE_CODE,
                                                 CHANNEL_TYPE,
                                                 MIN(CHN.START_DATE) ACCURATE_START_DATE   
                                  FROM (SELECT EMP.EMPLOYEE_CODE,
                                               HIS.CHANNEL_TYPE,
                                               HIS.CHANNEL_DUTY,
                                               HIS.START_DATE,
                                               HIS.END_DATE,
                                               ROW_NUMBER() OVER(ORDER BY EMP.EMPLOYEE_CODE, HIS.START_DATE) - ROW_NUMBER() OVER(PARTITION BY EMP.EMPLOYEE_CODE, HIS.CHANNEL_TYPE ORDER BY EMP.EMPLOYEE_CODE, HIS.START_DATE) IND
                                          FROM EPCIS_SAS_EMPLOYEE EMP ,EPCIS_SAS_LEVEL_HISTORY HIS
                                         WHERE EMP.HR_CODE IS NOT NULL
                                           AND EMP.EMPLOYEE_CODE = HIS.EMPLOYEE_CODE
                                           AND EMP.ENTER_DATE < date‘2014-09-01‘
                                           AND NVL(EMP.LEAVE_DATE, SYSDATE) >= date‘2014-10-01‘) CHN
                                   group by EMPLOYEE_CODE,CHANNEL_TYPE) enterchannel
                                where emp.employee_code = enterchannel.EMPLOYEE_CODE
                                  and emp.EMPLOYEE_CHANNEL = enterchannel.CHANNEL_TYPE) ACCURATE_START_DATE, --入渠时间
       EMP.EMPLOYEE_CODE,
       EMP.EMPLOYEE_NAME,
       EMP.EMPLOYEE_TYPE,
       EMP.EDUCATION_CODE, --学历
       TO_CHAR(TO_NUMBER(SUBSTR(‘20140901‘, 1, 4)) -
               TO_NUMBER(TO_CHAR(EMP.BIRTHDAY, ‘YYYY‘))) AS AGE, --年龄
       EMP.DEPARTMENT_CODE,
       nvl((SELECT GR.GROUP_CODE
             FROM EPCIS_SAS_EMP_GROUP_HISTORY GR
            WHERE GR.EMPLOYEE_CODE = EMP.EMPLOYEE_CODE
              AND GR.START_DATE <= TO_DATE(‘20140901‘, ‘YYYYMMDD‘)
              AND NVL(GR.END_DATE, TO_DATE(‘20141001‘, ‘YYYYMMDD‘)) >=
                  TO_DATE(‘20141001‘, ‘YYYYMMDD‘) - 1
              AND ROWNUM = 1),
           (SELECT GR.GROUP_CODE
              FROM EPCIS_SAS_EMP_GROUP_HISTORY GR
             WHERE GR.EMPLOYEE_CODE = EMP.EMPLOYEE_CODE
               AND GR.START_DATE >= TO_DATE(‘20140901‘, ‘YYYYMMDD‘)
               AND GR.START_DATE <= TO_DATE(‘20141001‘, ‘YYYYMMDD‘) - 1
               AND NVL(GR.END_DATE, TO_DATE(‘20141001‘, ‘YYYYMMDD‘)) >=
                   TO_DATE(‘20141001‘, ‘YYYYMMDD‘) - 1
               AND ROWNUM = 1)) AS GROUP_CODE, --机构
       TO_CHAR(CASE
                 WHEN EMP.ENTER_DATE <
                      TO_DATE(‘20140901‘, ‘YYYYMMDD‘) AND
                      NVL(EMP.LEAVE_DATE,
                          TO_DATE(‘20141001‘, ‘YYYYMMDD‘)) >=
                      TO_DATE(‘20141001‘, ‘YYYYMMDD‘) THEN
                  0
                 WHEN TO_CHAR(EMP.ENTER_DATE, ‘YYYYMM‘) =
                      SUBSTR(‘20140901‘, 1, 6) AND NVL(EMP.LEAVE_DATE,
                                                       TO_DATE(‘20141001‘, ‘YYYYMMDD‘)) >=
                      TO_DATE(‘20141001‘, ‘YYYYMMDD‘) THEN
                  1
                 WHEN TO_CHAR(EMP.LEAVE_DATE, ‘YYYYMM‘) =
                      SUBSTR(‘20140901‘, 1, 6) AND EMP.ENTER_DATE <
                      TO_DATE(‘20141001‘, ‘YYYYMMDD‘) THEN
                  2
                 WHEN EMP.LEAVE_DATE <
                      TO_DATE(‘20140901‘, ‘YYYYMMDD‘) AND
                      EMP.ENTER_DATE <
                      TO_DATE(‘20141001‘, ‘YYYYMMDD‘) THEN
                  3
                 ELSE
                  4
               END) AS NEW_ENTER_FLAG, --入司标志 0--在职老员工,1--新员工,2-当月离职,3--当月之前离职,4-异常
       NVL((SELECT A.CHANNEL_TYPE
             FROM EPCIS_SAS_LEVEL_HISTORY A
            WHERE A.EMPLOYEE_CODE = emp.EMPLOYEE_CODE
              AND A.START_DATE <= TO_DATE(‘20140901‘, ‘YYYYMMDD‘)
              AND NVL(A.END_DATE, TO_DATE(‘20141001‘, ‘YYYYMMDD‘)) >=
                  TO_DATE(‘20141001‘, ‘YYYYMMDD‘) - 1
              AND ROWNUM = 1),--当月人员渠道
           emp.EMPLOYEE_CHANNEL) AS EMPLOYEE_CHANNEL,
       NVL((SELECT A.CHANNEL_DUTY
             FROM EPCIS_SAS_LEVEL_HISTORY A
            WHERE A.EMPLOYEE_CODE = EMP.EMPLOYEE_CODE
              AND A.START_DATE <= TO_DATE(‘20140901‘, ‘YYYYMMDD‘)
              AND NVL(A.END_DATE, TO_DATE(‘20141001‘, ‘YYYYMMDD‘)) >=
                  TO_DATE(‘20141001‘, ‘YYYYMMDD‘) - 1
              AND ROWNUM = 1),
           EMP.CHANNEL_DUTY) AS CHANNEL_DUTY,--人员类别
        
        case when (select T.EMPLOYEE_CODE
                     from EPCIS_SAS_DISMISS_DEGRADE t
                    WHERE T.DEGRADE_TYPE = ‘D‘
                      AND T.date_begin_date  < to_date(‘20141001‘,‘yyyymmdd‘)
                      AND T.date_end_date  >= to_date(‘20140901‘,‘yyyymmdd‘)
                      AND T.EMPLOYEE_CODE = emp.employee_code
                      AND T.CONFIRM_FLAG = ‘1‘) is not null then 1
                when (select sum(PREMIUM_PLAN) from 
                      (SELECT T.PREMIUM_PLAN,
                             t.employee_code
                        FROM POLAPDATA.EPCIS_SAP_EMP_PREMIUM_PLAN T,EPCIS_SAS_EMPLOYEE t2
                       WHERE T.CHANNEL = ‘DS‘ --直销
                         AND T.EMPLOYEE_CODE = t2.employee_code
                         AND T.DUTY = t2.CHANNEL_DUTY
                         AND T.CHANNEL =  t2.CHANNEL_DUTY
                         AND YEAR = substr(‘20140901‘,1,4)
                         AND MONTH IS NULL
                         AND T.PREMIUM_PLAN_TYPE = ‘ACCUMULATET‘
                      UNION ALL
                        SELECT T.PREMIUM_PLAN,
                               t.employee_code
                          FROM POLAPDATA.EPCIS_SAP_EMP_PREMIUM_PLAN T,EPCIS_SAS_EMPLOYEE t2
                         WHERE T.CHANNEL = ‘BS‘ --重点客户
                           AND T.EMPLOYEE_CODE = t2.employee_code
                           AND DUTY = t2.CHANNEL_DUTY
                           AND T.CHANNEL =  t2.CHANNEL_DUTY
                           AND YEAR = substr(‘20140901‘,1,4)
                           AND MONTH IS NULL
                           AND T.PREMIUM_PLAN_TYPE = ‘ACCUMULATET‘
                      UNION ALL
                        SELECT T.PREMIUM_PLAN,
                               t.employee_code
                          FROM POLAPDATA.EPCIS_SAP_EMP_PREMIUM_PLAN T,EPCIS_SAS_EMPLOYEE t2
                         WHERE T.CHANNEL = ‘IS‘ --银保
                           AND T.EMPLOYEE_CODE = t2.employee_code
                           AND T.DUTY = t2.CHANNEL_DUTY
                           AND T.CHANNEL =  t2.CHANNEL_DUTY
                           AND YEAR = substr(‘20140901‘,1,4)
                           AND MONTH IS NULL
                           AND T.PREMIUM_PLAN_TYPE = ‘TT‘) sum_plan
                    where sum_plan.employee_code = emp.employee_code        
                  group by employee_code) > (SELECT A.STANDARD_AMOUNT
                                                FROM (SELECT A.STANDARD_AMOUNT,
                                                             E.EMPLOYEE_CODE
                                                        FROM POLAPDATA.EPCIS_SAS_DIRECT_DEGRADE_SET A,
                                                             epcis_DEPARTMENT_DEFINE      B,
                                                             EPCIS_SAS_EMPLOYEE E
                                                       WHERE A.DEPARTMENT_CODE = B.DEPARTMENT_CODE
                                                         AND E.DEPARTMENT_CODE = B.DEPARTMENT_CODE
                                                         AND (A.EFFECTIVE_DATE <=
                                                               trunc(TO_DATE(‘20140901‘, ‘yyyymmdd‘),‘mm‘) AND
                                                             NVL(A.INVALID_DATE, SYSDATE) >
                                                              trunc(TO_DATE(‘20140901‘, ‘yyyymmdd‘),‘mm‘) AND
                                                             NVL(B.INVALIDATE_DATE, SYSDATE) >=
                                                              trunc(TO_DATE(‘20140901‘, ‘yyyymmdd‘),‘mm‘))
                                                         AND A.PREMIUM_TYPE = ‘B‘ -- B为实收保费底线， A为标准底线   
                                                       ORDER BY B.DEPARTMENT_LEVEL DESC) A
                                              WHERE  A.EMPLOYEE_CODE =emp.employee_code and ROWNUM = 1) then 2 --猎鹰
                    else 3 --普通
                    end end ,
        case when substr(‘20140901‘,1,4) < ‘2015‘ then (select SUM(a.COLLECT_AMOUNT) from epcis_SAS_COLLECT_CHANNEL a where substr(a.calc_month,1,4) = substr(‘20140901‘,1,4) - 1 and a.employee_code = EMP.EMPLOYEE_CODE)
             when substr(‘20140901‘,1,4) > ‘2015‘ and to_char(sysdate,‘yyyy‘) = ‘2015‘ then (select  SUM(a.COLLECT_AMOUNT) from epcis_SAS_COLLECT_CHANNEL a where substr(a.calc_month,1,4) = substr(‘20140901‘,1,4) - 1 and a.employee_code = EMP.EMPLOYEE_CODE)
             when substr(‘20140901‘,1,4) > ‘2015‘ and to_char(sysdate,‘yyyy‘) > ‘2015‘ then (select b.COLLECT_AMOUNT from  POLAPDATA.EPCIS_SAP_COLLECT_CH_EMPDAILY b where to_char(b.account_date,‘yyyy‘) = substr(‘20140901‘,1,4) -1 and b.employee_code = EMP.EMPLOYEE_CODE)
         end  last_premium,  --上年保费
        case when substr(‘20140901‘,1,4) < ‘2015‘ then (select SUM(a.COLLECT_AMOUNT) from epcis_SAS_COLLECT_CHANNEL a where substr(a.calc_month,1,6) = substr(‘20140901‘,1,6) and a.employee_code = EMP.EMPLOYEE_CODE)
             when substr(‘20140901‘,1,4) >= ‘2015‘then (select b.COLLECT_AMOUNT from  POLAPDATA.EPCIS_SAP_COLLECT_CH_EMPDAILY b where to_char(b.account_date,‘yyyymm‘) = substr(‘20140901‘,1,6) and b.employee_code = EMP.EMPLOYEE_CODE)
         end  monthfee_onjob,--当月保费
        case when substr(‘20140901‘,1,4) < ‘2015‘ then (select SUM(a.COLLECT_AMOUNT) from epcis_SAS_COLLECT_CHANNEL a where substr(a.calc_month,1,4) = substr(‘20140901‘,1,4) and a.employee_code = EMP.EMPLOYEE_CODE)
             when substr(‘20140901‘,1,4) >= ‘2015‘then (select b.COLLECT_AMOUNT from  POLAPDATA.EPCIS_SAP_COLLECT_CH_EMPDAILY b where to_char(b.account_date,‘yyyy‘) = substr(‘20140901‘,1,4) and b.employee_code = EMP.EMPLOYEE_CODE)
         end  monthfee_onjob,--当年保费
        (select CLIENT_NUM from (  SELECT A.SALE_AGENT_CODE, --业务员编码
                                          COUNT(DISTINCT B.CLIENT_NAME) AS CLIENT_NUM --唯一客户数
                                      FROM (SELECT A.POLICY_NO, A.SALE_AGENT_CODE
                                              FROM EPCIS_AUTO_POLICY_BASE_INFO A
                                             WHERE A.UNDERWRITE_TIME >= TRUNC(to_date(‘20140901‘,‘yyyymmdd‘),‘YYYY‘)
                                               AND A.UNDERWRITE_TIME < to_date(‘20141001‘,‘yyyymmdd‘)
                                               AND A.BUSINESS_TYPE = ‘2‘
                                               AND A.CANEL_MARK = ‘N‘
                                            UNION ALL
                                            SELECT A.POLICY_NO, A.SALE_AGENT_CODE
                                              FROM EPCIS_NL_POLICY A
                                             WHERE A.UNDERWRITE_TIME >= TRUNC(to_date(‘20140901‘,‘yyyymmdd‘),‘YYYY‘)
                                               AND A.UNDERWRITE_TIME < to_date(‘20141001‘,‘yyyymmdd‘)
                                               AND A.BUSINESS_TYPE = ‘2‘
                                               AND A.CANcEL_MARK = ‘N‘
                                            UNION ALL
                                            SELECT A.POLICY_NO, A.SALE_AGENT_CODE
                                              FROM EPCIS_ACC_POLICY_BASE_INFO A
                                             WHERE A.ACCEPT_INSURANCE_DATE >= TRUNC(to_date(‘20140901‘,‘yyyymmdd‘),‘YYYY‘)
                                               AND A.ACCEPT_INSURANCE_DATE < to_date(‘20141001‘,‘yyyymmdd‘)
                                               AND A.BUSINESS_TYPE = ‘2‘
                                               AND A.CANcEL_MARK = ‘N‘) A,
                                           EPCIS_ECIF_CLIENT_INFO_APPLY B --团体客户保单信息表
                                    WHERE A.POLICY_NO = B.POLICY_NO(+)
                                    GROUP BY A.SALE_AGENT_CODE) customersum
                 where customersum.SALE_AGENT_CODE = emp.employee_code)--客户数
  from EPCIS_SAS_EMPLOYEE emp;

sql

标签：