본문 바로가기
작업일지

고객수 현황(EIS개발)

소마후니 2015. 9. 14. 18:49

--DEFINE BASE_DT = '20090403'

--FetClntCnt00
--고객수 현황
SELECT /*+ RULE */ /*+MG$FetClntCnt00 작성자 영업지원_황해훈*/
       VVV1.SEQ                             /*!type long   */ /*!시퀀스     */ SEQ,
       NVL(VVV2.CNT,0)                      /*!type long   */ /*!목표수     */ CNT1,
       NVL(VVV1.CNT1,0)                     /*!type long   */ /*!당일고객수 */ CNT2,
       NVL(VVV1.CNT1 - VVV1.CNT2,0)         /*!type long   */ /*!전일고객수 */ CNT3,
       NVL(VVV1.CNT1 - VVV1.CNT3,0)         /*!type long   */ /*!전월고객수 */ CNT4,
       NVL(VVV1.CNT1 - VVV1.CNT4,0)         /*!type long   */ /*!전년고객수 */ CNT5,
       NVL(VVV1.CNT1 / VVV2.CNT * 100,0)    /*!type double */ /*!진척도   */ ACHV_RAT
FROM (
 SELECT
        1 SEQ,
        SUM(DECODE(VV1.DPSAST_AMT1,0,0,1)) CNT1,
        SUM(DECODE(VV1.DPSAST_AMT2,0,0,1)) CNT2,
        SUM(DECODE(VV1.DPSAST_AMT3,0,0,1)) CNT3,
        SUM(DECODE(VV1.DPSAST_AMT4,0,0,1)) CNT4
 FROM (
  SELECT
         DISTINCT A.CLNT_NO,
         SUM(CASE WHEN V1.BASE_DT = /*!type char(8)*/ /*!기준일 */:BASE_DT
                  THEN DPSAST_AMT
                  ELSE 0
                  END)DPSAST_AMT1,
         SUM(CASE WHEN V1.BASE_DT = TO_CHAR(TO_DATE(:BASE_DT,'YYYYMMDD')-1,'YYYYMMDD')
                  THEN DPSAST_AMT
                  ELSE 0
                  END)DPSAST_AMT2,
         SUM(CASE WHEN V1.BASE_DT = TO_CHAR(LAST_DAY(ADD_MONTHS(TO_DATE(:BASE_DT,'YYYYMMDD'),-1)),'YYYYMMDD')
                  THEN DPSAST_AMT
                  ELSE 0
                  END)DPSAST_AMT3,
         SUM(CASE WHEN V1.BASE_DT = SUBSTR(TO_CHAR(ADD_MONTHS(TO_DATE(/*!type char(8)*/ /*!기준일 */:BASE_DT,'YYYYMMDD'),-12),'YYYYMMDD'),1,4)||'1231'
                  THEN DPSAST_AMT
                  ELSE 0
                  END)DPSAST_AMT4


  FROM (
   SELECT
        ACNT_NO             ACNT_NO
        ,BASE_DT            BASE_DT
        ,SUM(DPS + STK_EVAL_AMT - CRDT_MLOAN_AMT)        DPSAST_AMT         --주식(위탁)
   FROM
       SL_TBD_DT_CSGN_ACNT_DPSAST
   WHERE  BASE_DT     IN (
                      :BASE_DT, --당일
                      TO_CHAR(TO_DATE(:BASE_DT,'YYYYMMDD')-1,'YYYYMMDD'), --전일
                      TO_CHAR(LAST_DAY(ADD_MONTHS(TO_DATE(:BASE_DT,'YYYYMMDD'),-1)),'YYYYMMDD'), --전월
                      SUBSTR(TO_CHAR(ADD_MONTHS(TO_DATE(/*!type char(8)*/ /*!기준일 */:BASE_DT,'YYYYMMDD'),-12),'YYYYMMDD'),1,4)||'1231' --전년
                      )
   GROUP BY ACNT_NO,BASE_DT

      UNION ALL

   SELECT
        ACNT_NO
        ,BASE_DT
           ,SUM(STK_EVAL_AMT + DPS)                           --주식(저축)
      FROM
       SL_TBD_DT_SAV_ACNT_DPSAST
   WHERE  BASE_DT     IN (
                      :BASE_DT, --당일
                      TO_CHAR(TO_DATE(:BASE_DT,'YYYYMMDD')-1,'YYYYMMDD'), --전일
                      TO_CHAR(LAST_DAY(ADD_MONTHS(TO_DATE(:BASE_DT,'YYYYMMDD'),-1)),'YYYYMMDD'), --전월
                      SUBSTR(TO_CHAR(ADD_MONTHS(TO_DATE(/*!type char(8)*/ /*!기준일 */:BASE_DT,'YYYYMMDD'),-12),'YYYYMMDD'),1,4)||'1231' --전년
                      )
   GROUP BY ACNT_NO,BASE_DT

      UNION ALL

   SELECT
        ACNT_NO
        ,BASE_DT
        ,SUM(DPS + OPT_EVAL_AMT)
   FROM
       SL_TBD_DT_FNO_ACNT_DPSAST
   WHERE  BASE_DT   IN (
                      :BASE_DT, --당일
                      TO_CHAR(TO_DATE(:BASE_DT,'YYYYMMDD')-1,'YYYYMMDD'), --전일
                      TO_CHAR(LAST_DAY(ADD_MONTHS(TO_DATE(:BASE_DT,'YYYYMMDD'),-1)),'YYYYMMDD'), --전월
                      SUBSTR(TO_CHAR(ADD_MONTHS(TO_DATE(/*!type char(8)*/ /*!기준일 */:BASE_DT,'YYYYMMDD'),-12),'YYYYMMDD'),1,4)||'1231' --전년
                      )
   AND EMP_NO NOT IN ('999999999')
   GROUP BY ACNT_NO,BASE_DT

      UNION ALL

      SELECT
           C.ACNT_NO
           ,BASE_DT
        ,SUM(EVAL_AMT)
      FROM CM_TBM_CODE            A,
          CM_TBM_IVSTFND         B,
          SL_TBL_ACNT_IVTRST_BAL C
   WHERE A.CMN_CSLDT_CODE = B.INDRC_INVST_CODE
   AND   B.IVSTFND_CODE   = C.IVSTFND_CODE
   AND   C.BASE_DT    IN (
                      :BASE_DT, --당일
                      TO_CHAR(TO_DATE(:BASE_DT,'YYYYMMDD')-1,'YYYYMMDD'), --전일
                      TO_CHAR(LAST_DAY(ADD_MONTHS(TO_DATE(:BASE_DT,'YYYYMMDD'),-1)),'YYYYMMDD'), --전월
                      SUBSTR(TO_CHAR(ADD_MONTHS(TO_DATE(/*!type char(8)*/ /*!기준일 */:BASE_DT,'YYYYMMDD'),-12),'YYYYMMDD'),1,4)||'1231' --전년
                      )
   AND   A.CODE_RANGE     = '0802'
   AND   A.LANG_TP        = 'K'
   GROUP BY ACNT_NO,BASE_DT

      UNION ALL

   SELECT
        B.ACNT_NO
        ,B.BASE_DT
        ,SUM(DPS)
   FROM BM_TBM_DT_CF_ACNT B
   WHERE B.BASE_DT IN  (
                     :BASE_DT, --당일
                     TO_CHAR(TO_DATE(:BASE_DT,'YYYYMMDD')-1,'YYYYMMDD'), --전일
                     TO_CHAR(LAST_DAY(ADD_MONTHS(TO_DATE(:BASE_DT,'YYYYMMDD'),-1)),'YYYYMMDD'), --전월
                     SUBSTR(TO_CHAR(ADD_MONTHS(TO_DATE(/*!type char(8)*/ /*!기준일 */:BASE_DT,'YYYYMMDD'),-12),'YYYYMMDD'),1,4)||'1231' --전년
                     )
   AND PDPTN_CODE = '04'
   AND HIRNK_ACNT_PDPTN_CODE <> '00'
   GROUP BY B.ACNT_NO,B.BASE_DT
  )V1
  , BM_TBM_ACNT A
  , BM_TBM_CLNT B
  WHERE V1.ACNT_NO = A.ACNT_NO
  AND   A.CLNT_NO  = B.CLNT_NO
  AND   A.PDPTN_CODE <> '00'
  AND   V1.DPSAST_AMT >= 30000000
  GROUP BY A.CLNT_NO
 )VV1

 UNION ALL

    -- 총고객수
 SELECT
        2 SEQ,
        SUM(DECODE(V1.CNT1,NULL,0,1)) CNT1,
        SUM(DECODE(V1.CNT2,NULL,0,1)) CNT2,
        SUM(DECODE(V1.CNT3,NULL,0,1)) CNT3,
        SUM(DECODE(V1.CNT4,NULL,0,1)) CNT4
 FROM (
  SELECT
         DISTINCT
         CASE WHEN BASE_DT = :BASE_DT
              THEN CLNT_NO
              END CNT1,
         CASE WHEN BASE_DT = TO_CHAR(TO_DATE(:BASE_DT,'YYYYMMDD')-1,'YYYYMMDD')
              THEN CLNT_NO
              END CNT2,
         CASE WHEN BASE_DT = TO_CHAR(LAST_DAY(ADD_MONTHS(TO_DATE(:BASE_DT,'YYYYMMDD'),-1)),'YYYYMMDD')
              THEN CLNT_NO
              END CNT3,
         CASE WHEN BASE_DT = SUBSTR(TO_CHAR(ADD_MONTHS(TO_DATE(/*!type char(8)*/ /*!기준일 */:BASE_DT,'YYYYMMDD'),-12),'YYYYMMDD'),1,4)||'1231'
              THEN CLNT_NO
              END CNT4
  FROM
   BM_TBM_DT_CF_ACNT
  WHERE BASE_DT IN (
                    :BASE_DT, --당일
                    TO_CHAR(TO_DATE(:BASE_DT,'YYYYMMDD')-1,'YYYYMMDD'), --전일
                    TO_CHAR(LAST_DAY(ADD_MONTHS(TO_DATE(:BASE_DT,'YYYYMMDD'),-1)),'YYYYMMDD'), --전월
                    SUBSTR(TO_CHAR(ADD_MONTHS(TO_DATE(/*!type char(8)*/ /*!기준일 */:BASE_DT,'YYYYMMDD'),-12),'YYYYMMDD'),1,4)||'1231' --전년
                    )
  AND PDPTN_CODE  IN ('01', '02', '03', '04')
  AND ACNT_STAT_CODE  = '00'
  AND ACNT_NO  > '0'
  AND ACNT_NO_CLSS_PTN_CODE = '01'
     )V1

        UNION ALL

        SELECT
      3 SEQ,
      SUM(CASE WHEN BASE_DT = :BASE_DT
               THEN PRDAY_ACTV_ACNT_QTY
                 +NEW_ACNT_QTY
                 +MVIN_ACNT_QTY
                 +CSLDT_ABND_ACNT_QTY
                 +PRDAY_CSLDT_PRERG_ACNT_QTY
                 +CLOSE_ABND_ACNT_QTY
                 -CSLDT_ACNT_QTY
                 -MVOUT_ACNT_QTY
                 -CLOSE_ACNT_QTY
            ELSE 0
            END) CNT1,
      SUM(CASE WHEN BASE_DT = TO_CHAR(TO_DATE(:BASE_DT,'YYYYMMDD')-1,'YYYYMMDD')
               THEN PRDAY_ACTV_ACNT_QTY
                 +NEW_ACNT_QTY
                 +MVIN_ACNT_QTY
                 +CSLDT_ABND_ACNT_QTY
                 +PRDAY_CSLDT_PRERG_ACNT_QTY
                 +CLOSE_ABND_ACNT_QTY
                 -CSLDT_ACNT_QTY
                 -MVOUT_ACNT_QTY
                 -CLOSE_ACNT_QTY
            ELSE 0
            END) CNT2,
      SUM(CASE WHEN BASE_DT = TO_CHAR(LAST_DAY(ADD_MONTHS(TO_DATE(:BASE_DT,'YYYYMMDD'),-1)),'YYYYMMDD')
               THEN PRDAY_ACTV_ACNT_QTY
                 +NEW_ACNT_QTY
                 +MVIN_ACNT_QTY
                 +CSLDT_ABND_ACNT_QTY
                 +PRDAY_CSLDT_PRERG_ACNT_QTY
                 +CLOSE_ABND_ACNT_QTY
                 -CSLDT_ACNT_QTY
                 -MVOUT_ACNT_QTY
                 -CLOSE_ACNT_QTY
            ELSE 0
            END) CNT3,
      SUM(CASE WHEN BASE_DT = SUBSTR(TO_CHAR(ADD_MONTHS(TO_DATE(/*!type char(8)*/ /*!기준일 */:BASE_DT,'YYYYMMDD'),-12),'YYYYMMDD'),1,4)||'1231'
               THEN PRDAY_ACTV_ACNT_QTY
                 +NEW_ACNT_QTY
                 +MVIN_ACNT_QTY
                 +CSLDT_ABND_ACNT_QTY
                 +PRDAY_CSLDT_PRERG_ACNT_QTY
                 +CLOSE_ABND_ACNT_QTY
                 -CSLDT_ACNT_QTY
                 -MVOUT_ACNT_QTY
                 -CLOSE_ACNT_QTY
            ELSE 0
            END) CNT4

  FROM
   AC_TBM_ACNT_QTY_AGRGT
  WHERE
       BASE_DT IN (
                    :BASE_DT, --당일
                    TO_CHAR(TO_DATE(:BASE_DT,'YYYYMMDD')-1,'YYYYMMDD'), --전일
                    TO_CHAR(LAST_DAY(ADD_MONTHS(TO_DATE(:BASE_DT,'YYYYMMDD'),-1)),'YYYYMMDD'), --전월
                    SUBSTR(TO_CHAR(ADD_MONTHS(TO_DATE(/*!type char(8)*/ /*!기준일 */:BASE_DT,'YYYYMMDD'),-12),'YYYYMMDD'),1,4)||'1231' --전년
                    )
  AND PRDT_DTL_CODE = '13' --CMA전용위탁
  GROUP BY PDPTN_CODE, PRDT_DTL_CODE

        UNION ALL

        SELECT
      4 SEQ,
   COUNT(CASE WHEN ACNT_OPN_DT = :BASE_DT
              THEN ACNT_NO
              END) CNT1,
   COUNT(CASE WHEN ACNT_OPN_DT = TO_CHAR(TO_DATE(:BASE_DT,'YYYYMMDD')-1,'YYYYMMDD')
              THEN ACNT_NO
              END) CNT2,
   COUNT(CASE WHEN ACNT_OPN_DT = TO_CHAR(LAST_DAY(ADD_MONTHS(TO_DATE(:BASE_DT,'YYYYMMDD'),-1)),'YYYYMMDD')
              THEN ACNT_NO
              END) CNT3,
   COUNT(CASE WHEN ACNT_OPN_DT = SUBSTR(TO_CHAR(ADD_MONTHS(TO_DATE(/*!type char(8)*/ /*!기준일 */:BASE_DT,'YYYYMMDD'),-12),'YYYYMMDD'),1,4)||'1231'
              THEN ACNT_NO
              END) CNT4
  FROM
   BM_TBM_ACNT
  WHERE
      ACNT_OPN_DT  IN (
                     :BASE_DT, --당일
                     TO_CHAR(TO_DATE(:BASE_DT,'YYYYMMDD')-1,'YYYYMMDD'), --전일
                     TO_CHAR(LAST_DAY(ADD_MONTHS(TO_DATE(:BASE_DT,'YYYYMMDD'),-1)),'YYYYMMDD'), --전월
                     SUBSTR(TO_CHAR(ADD_MONTHS(TO_DATE(/*!type char(8)*/ /*!기준일 */:BASE_DT,'YYYYMMDD'),-12),'YYYYMMDD'),1,4)||'1231' --전년
                     )
  AND ACNT_STAT_CODE IN ( '00', '09')
  AND PDPTN_CODE = '00'
  )VVV1,
  (
 SELECT DECODE(UNDGL_CODE,'009',1,'010',2,'011',3,'012',4) SEQ
       ,SUM(MTH_TRGT_VAL) CNT
 FROM MG_TBL_MTHLY_UNDGL
 WHERE BASE_YYMM = SUBSTR(:BASE_DT,1,6)
 AND AGRGT_ORGZ_CLSS_CODE = '01' --자산
 AND UNDGL_CODE IN ('009','010','011','012')
 GROUP BY DECODE(UNDGL_CODE,'009',1,'010',2,'011',3,'012',4)
 )VVV2
WHERE VVV1.SEQ = VVV2.SEQ(+)
ORDER BY VVV1.SEQ

 

저작자표시 비영리 변경금지

'작업일지' 카테고리의 다른 글

20100605_Migration 작업  (0) 2015.09.14
20100705~20100707 Tibero Exp,Imp 작업  (0) 2015.09.14
금융상품실적현황(EIS개발)  (0) 2015.09.14
위탁약정현황(EIS개발)  (0) 2015.09.14
날짜 조회(EIS개발)  (0) 2015.09.14

'작업일지' Related Articles

티스토리툴바