SQL 횡전개 응용 ORACLE 기준 기본

IT/SQL

SQL 횡전개 응용 ORACLE 기준 기본

zolla 2011. 4. 15. 13:33

WITH TBDWMERSMBMORCD AS(
    SELECT 100 LUMP_NSALE_AMT, 50 INS_NSALE_AMT, 200 SPAY_AMT, TO_CHAR(add_months(SYSDATE,-1),'YYYYMM') ACPT_YM, 20 MB_NO
    FROM   DUAL
    UNION ALL
    SELECT 100 LUMP_NSALE_AMT, 50 INS_NSALE_AMT, 200 SPAY_AMT, TO_CHAR(add_months(SYSDATE,-2),'YYYYMM') ACPT_YM, 20 MB_NO
    FROM   DUAL
    UNION ALL
    SELECT 100 LUMP_NSALE_AMT, 50 INS_NSALE_AMT, 200 SPAY_AMT, TO_CHAR(add_months(SYSDATE,-3),'YYYYMM') ACPT_YM, 20 MB_NO
    FROM   DUAL
    UNION ALL
    SELECT 100 LUMP_NSALE_AMT, 50 INS_NSALE_AMT, 200 SPAY_AMT, TO_CHAR(add_months(SYSDATE,-4),'YYYYMM') ACPT_YM, 20 MB_NO
    FROM   DUAL
    UNION ALL
    SELECT 100 LUMP_NSALE_AMT, 50 INS_NSALE_AMT, 200 SPAY_AMT, TO_CHAR(add_months(SYSDATE,-5),'YYYYMM') ACPT_YM, 20 MB_NO
    FROM   DUAL
    UNION ALL
    SELECT 100 LUMP_NSALE_AMT, 50 INS_NSALE_AMT, 200 SPAY_AMT, TO_CHAR(add_months(SYSDATE,-6),'YYYYMM') ACPT_YM, 20 MB_NO
    FROM   DUAL
    UNION ALL
    SELECT 100 LUMP_NSALE_AMT, 50 INS_NSALE_AMT, 200 SPAY_AMT, TO_CHAR(add_months(SYSDATE,-1),'YYYYMM') ACPT_YM, 10 MB_NO
    FROM   DUAL
    UNION ALL
    SELECT 100 LUMP_NSALE_AMT, 50 INS_NSALE_AMT, 200 SPAY_AMT, TO_CHAR(add_months(SYSDATE,-2),'YYYYMM') ACPT_YM, 10 MB_NO
    FROM   DUAL
    UNION ALL
    SELECT 100 LUMP_NSALE_AMT, 50 INS_NSALE_AMT, 200 SPAY_AMT, TO_CHAR(add_months(SYSDATE,-3),'YYYYMM') ACPT_YM, 10 MB_NO
    FROM   DUAL
    UNION ALL
    SELECT 100 LUMP_NSALE_AMT, 50 INS_NSALE_AMT, 200 SPAY_AMT, TO_CHAR(add_months(SYSDATE,-4),'YYYYMM') ACPT_YM, 10 MB_NO
    FROM   DUAL
    UNION ALL
    SELECT 100 LUMP_NSALE_AMT, 50 INS_NSALE_AMT, 200 SPAY_AMT, TO_CHAR(add_months(SYSDATE,-5),'YYYYMM') ACPT_YM, 10 MB_NO
    FROM   DUAL
    UNION ALL
    SELECT 100 LUMP_NSALE_AMT, 50 INS_NSALE_AMT, 200 SPAY_AMT, TO_CHAR(add_months(SYSDATE,-6),'YYYYMM') ACPT_YM, 10 MB_NO
    FROM   DUAL
)
SELECT 
    LV,        
    SUM( CASE WHEN MB_NO = '20' AND TO_CHAR(add_months(sysdate,-5),'yyyymm') = ACPT_YM THEN DECODE(LV,1,LUMP_NSALE_AMT,2,INS_NSALE_AMT,3,SPAY_AMT) END ) SALE_AMT_5, 
    SUM( CASE WHEN MB_NO = '20' AND TO_CHAR(add_months(sysdate,-4),'yyyymm') = ACPT_YM THEN DECODE(LV,1,LUMP_NSALE_AMT,2,INS_NSALE_AMT,3,SPAY_AMT) END ) SALE_AMT_4, 
    SUM( CASE WHEN MB_NO = '20' AND TO_CHAR(add_months(sysdate,-3),'yyyymm') = ACPT_YM THEN DECODE(LV,1,LUMP_NSALE_AMT,2,INS_NSALE_AMT,3,SPAY_AMT) END ) SALE_AMT_3, 
    SUM( CASE WHEN MB_NO = '20' AND TO_CHAR(add_months(sysdate,-2),'yyyymm') = ACPT_YM THEN DECODE(LV,1,LUMP_NSALE_AMT,2,INS_NSALE_AMT,3,SPAY_AMT) END ) SALE_AMT_2, 
    SUM( CASE WHEN MB_NO = '20' AND TO_CHAR(add_months(sysdate,-1),'yyyymm') = ACPT_YM THEN DECODE(LV,1,LUMP_NSALE_AMT,2,INS_NSALE_AMT,3,SPAY_AMT) END ) SALE_AMT_1,
    SUM( CASE WHEN MB_NO = '20' AND TO_CHAR(sysdate,'yyyymm') = ACPT_YM THEN DECODE(LV,1,LUMP_NSALE_AMT,2,INS_NSALE_AMT,3,SPAY_AMT) END ) SALE_AMT,
    SUM( DECODE(LV,1,LUMP_NSALE_AMT,2,INS_NSALE_AMT,3,SPAY_AMT) ) AMT_TOT
FROM   TBDWMERSMBMORCD, (SELECT level lv FROM dual CONNECT BY level <= 3)
--WHERE to_date(ACPT_YM,'yyyymm') < to_date(TO_CHAR(sysdate,'yyyymm'),'yyyymm')
WHERE ACPT_YM IN ( TO_CHAR(add_months(sysdate,-5),'yyyymm'), 
                   TO_CHAR(add_months(sysdate,-4),'yyyymm'), 
                   TO_CHAR(add_months(sysdate,-3),'yyyymm'), 
                   TO_CHAR(add_months(sysdate,-2),'yyyymm'), 
                   TO_CHAR(add_months(sysdate,-1),'yyyymm'),
                   TO_CHAR(sysdate,'yyyymm') )
                       --AND to_date(ACPT_YM,'yyyymm') > add_months(sysdate,-6)
GROUP BY CUBE (LV) --, (LV) )