?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
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) )
TOP을 쫓는 술래zolla 님의 블로그입니다.
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
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
    NVL(MB_NO, 'TOTAL') MB_NO,
    NVL(LV, 'TOTAL') LV,
    SALE_AMT_5, SALE_AMT_4, SALE_AMT_3, SALE_AMT_2, SALE_AMT_1, SALE_AMT
FROM (
    SELECT
        MB_NO||'' MB_NO,LV||'' LV,
        SUM( CASE WHEN 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 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 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 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 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 TO_CHAR(sysdate,'yyyymm') = ACPT_YM THEN DECODE(LV,1,LUMP_NSALE_AMT,2,INS_NSALE_AMT,3,SPAY_AMT) END ) SALE_AMT
    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 (MB_NO,LV) --, (LV) )
    --AND MER_MGMT_NO = '700000014'
)
WHERE MB_NO IS NULL OR MB_NO = 20
ORDER BY 1, 2
TOP을 쫓는 술래zolla 님의 블로그입니다.

+ Recent posts

티스토리툴바