기간내에 월 count 구하는 방법 문의합니다. 0 6 2,180

by 오라클초보 [SQL Query] 기간 count 월수 중복월수제외 [2015.09.24 23:41:15]


아래 표에 순번, 시작일자, 종료일자가 있을때 현재일을 기준으로 6개월 이내(즉 2015.03.25~2015.09.24)에서 월의 count를 구하는데 월이 같으면 1건으로  계산을 해야 합니다. 종료일자가 2015.09.24일보다 크거나 같으면 포함이 되므로 순번 1, 2, 3, 5, 6, 8, 9, 10 입니다. ([산출월]은 참조용입니다.)

결과는 7건이 나와야 합니다.

순번 시작일자 종료일자 산출월
1 20140510 99991231 201503 201504 201505 201506 201507 201508 201509
2 20140525 99991231 201503 201504 201505 201506 201507 201508 201509
3 20140610 99991231 201503 201504 201505 201506 201507 201508 201509
4 20140710 20150324              
5 20140710 20150420 201503 201504          
6 20150110 99991231 201503 201504 201505 201506 201507 201508 201509
7 20150110 20150324              
8 20150120 20150328 201503            
9 20150301 99991231   201504 201505 201506 201507 201508 201509
10 20150410 20150924   201504 201505 201506 201507 201508 201509
11 20140510 00000000              
12 20140520 00000000              
by 필상 [2015.09.25 09:04:19]

참고 하시기 바랍니다.

WITH T AS(
SELECT 1 AS C1, TO_DATE('20140510', 'YYYYMMDD') AS C2, TO_DATE('99991231', 'YYYYMMDD') AS C3 FROM DUAL UNION ALL
SELECT 2 AS C1, TO_DATE('20140525', 'YYYYMMDD') AS C2, TO_DATE('99991231', 'YYYYMMDD') AS C3 FROM DUAL UNION ALL
SELECT 3 AS C1, TO_DATE('20140710', 'YYYYMMDD') AS C2, TO_DATE('20150324', 'YYYYMMDD') AS C3 FROM DUAL UNION ALL
SELECT 4 AS C1, TO_DATE('20140510', 'YYYYMMDD') AS C2, TO_DATE('20150424', 'YYYYMMDD') AS C3 FROM DUAL)
SELECT A.C1, A.C2, A.C3, COUNT(C.TT) AS CNT
  FROM ( SELECT A.C1, A.C2, A.C3,
                TO_CHAR(GREATEST(A.C2, B.C1), 'YYYYMM') AS ST_MON, TO_CHAR(LEAST(A.C3, B.C2), 'YYYYMM') AS END_MON
           FROM T A,
                ( SELECT ADD_MONTHS(TRUNC(SYSDATE, 'DD'), -6) + 1 AS C1, TRUNC(SYSDATE, 'DD') AS C2 FROM DUAL ) B
          WHERE A.C2 <= B.C2(+)
            AND A.C3 >= B.C1(+) ) A,
       ( SELECT TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE, 'MM'), (LEVEL - 1 )* -1 ), 'YYYYMM') AS TT
           FROM DUAL
        CONNECT BY LEVEL <= 7 ) C
 WHERE C.TT(+) BETWEEN A.ST_MON AND A.END_MON
 GROUP BY A.C1, A.C2, A.C3
 ORDER BY C1


by 오라클초보 [2015.09.30 21:17:42]

감사합니다.


by 창조의날개 [2015.09.25 10:39:14]

WITH TT AS(
    SELECT 1 AS C1, '20140510' AS C2, '99991231' AS C3 FROM DUAL UNION ALL
    SELECT 2 AS C1, '20140525' AS C2, '99991231' AS C3 FROM DUAL UNION ALL
    SELECT 3 AS C1, '20140610' AS C2, '99991231' AS C3 FROM DUAL UNION ALL
    SELECT 4 AS C1, '20140710' AS C2, '20150324' AS C3 FROM DUAL UNION ALL
    SELECT 5 AS C1, '20140710' AS C2, '20150420' AS C3 FROM DUAL UNION ALL
    SELECT 6 AS C1, '20150110' AS C2, '99991231' AS C3 FROM DUAL UNION ALL
    SELECT 7 AS C1, '20150110' AS C2, '20150324' AS C3 FROM DUAL UNION ALL
    SELECT 8 AS C1, '20150120' AS C2, '20150328' AS C3 FROM DUAL UNION ALL
    SELECT 9 AS C1, '20150301' AS C2, '99991231' AS C3 FROM DUAL UNION ALL
    SELECT 10 AS C1, '20150410' AS C2, '20150924' AS C3 FROM DUAL UNION ALL
    SELECT 11 AS C1, '20140510' AS C2, '00000000' AS C3 FROM DUAL UNION ALL
    SELECT 12 AS C1, '20140520' AS C2, '00000000' AS C3 FROM DUAL
)
SELECT C1, C2, C3
     , LEAST(CEIL(6-MONTHS_BETWEEN(TRUNC(SYSDATE,'MM'), TO_DATE(DECODE(C3,'00000000',NULL,C3),'YYYYMMDD'))),6) 산출월수
FROM TT
WHERE TO_DATE(DECODE(C3,'00000000',NULL,C3),'YYYYMMDD') > ADD_MONTHS(SYSDATE,-6)
;

 


by 오라클초보 [2015.09.30 21:18:05]

감사합니다.


by 마농 [2015.09.25 14:41:56]
SELECT 순번, 시작일자, 종료일자
     , MONTHS_BETWEEN
       ( TO_DATE(SUBSTR(LEAST   (종료일자, edt), 1, 6), 'yyyymm')
       , TO_DATE(SUBSTR(GREATEST(시작일자, sdt), 1, 6), 'yyyymm')
       ) + 1 AS cnt
  FROM t
     , (SELECT TO_CHAR(ADD_MONTHS(sysdate, -6) + 1, 'yyyymmdd') sdt
             , TO_CHAR(sysdate, 'yyyymmdd') edt
          FROM dual
        )
 WHERE 시작일자 <= edt
   AND 종료일자 >= sdt
 ORDER BY 순번
;

 


by 오라클초보 [2015.09.30 21:19:18]

감사합니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입