분기별 신청가능건수 : 10건일 때, 지난달 초과건수를 조회할 수 있는 쿼리를 짜고 싶습니다.
WITH T (DT, EMPNO, MAINK) AS ( SELECT TO_DATE('20100101','YYYYMMDD'), '1001', '123456789' FROM DUAL UNION ALL SELECT TO_DATE('20200201','YYYYMMDD'), '1001', '223456789' FROM DUAL UNION ALL SELECT TO_DATE('20200301','YYYYMMDD'), '1001', '323456789' FROM DUAL UNION ALL SELECT TO_DATE('20200401','YYYYMMDD'), '1001', '423456789' FROM DUAL UNION ALL SELECT TO_DATE('20100101','YYYYMMDD'), '1001', '523456789' FROM DUAL UNION ALL SELECT TO_DATE('20200201','YYYYMMDD'), '1001', '623456789' FROM DUAL UNION ALL SELECT TO_DATE('20200301','YYYYMMDD'), '1001', '723456789' FROM DUAL UNION ALL SELECT TO_DATE('20200401','YYYYMMDD'), '1001', '823456789' FROM DUAL UNION ALL SELECT TO_DATE('20100101','YYYYMMDD'), '1001', '923456789' FROM DUAL UNION ALL SELECT TO_DATE('20200201','YYYYMMDD'), '1001', '193456789' FROM DUAL UNION ALL SELECT TO_DATE('20200301','YYYYMMDD'), '1001', '183456789' FROM DUAL UNION ALL SELECT TO_DATE('20200401','YYYYMMDD'), '1001', '173456789' FROM DUAL UNION ALL SELECT TO_DATE('20100101','YYYYMMDD'), '1001', '163456789' FROM DUAL UNION ALL SELECT TO_DATE('20200201','YYYYMMDD'), '1001', '153456789' FROM DUAL UNION ALL SELECT TO_DATE('20200301','YYYYMMDD'), '1001', '143456789' FROM DUAL UNION ALL SELECT TO_DATE('20200401','YYYYMMDD'), '1001', '133456789' FROM DUAL ) SELECT DT, EMPNO, COUNT(*) FROM T GROUP BY DT, EMPNO
위 쿼리로 봤을 때,
1월 4건, 2월 4건, 3월 4건으로
1월 초과 0건, 2월 초과 0건, 3월 초과 2건으로 출력하고 싶어요.
만약에 1월 4건 2월 8건, 3월 4건이라면,
1월 초과 0건, 2월 초과 2건, 3월 초과 4건으로 출력이 가능하게끔 쿼리를 짜려면 어떻게 해야할까요?
도움 부탁드립니다!
WITH t (dt, empno, maink) AS ( SELECT DATE '2020-01-01', '1001', '123456789' FROM dual UNION ALL SELECT DATE '2020-02-01', '1001', '223456789' FROM dual UNION ALL SELECT DATE '2020-03-01', '1001', '323456789' FROM dual UNION ALL SELECT DATE '2020-04-01', '1001', '423456789' FROM dual UNION ALL SELECT DATE '2020-01-01', '1001', '523456789' FROM dual UNION ALL SELECT DATE '2020-02-01', '1001', '623456789' FROM dual UNION ALL SELECT DATE '2020-03-01', '1001', '723456789' FROM dual UNION ALL SELECT DATE '2020-04-01', '1001', '823456789' FROM dual UNION ALL SELECT DATE '2020-01-01', '1001', '923456789' FROM dual UNION ALL SELECT DATE '2020-02-01', '1001', '193456789' FROM dual UNION ALL SELECT DATE '2020-03-01', '1001', '183456789' FROM dual UNION ALL SELECT DATE '2020-04-01', '1001', '173456789' FROM dual UNION ALL SELECT DATE '2020-01-01', '1001', '163456789' FROM dual UNION ALL SELECT DATE '2020-02-01', '1001', '153456789' FROM dual UNION ALL SELECT DATE '2020-03-01', '1001', '143456789' FROM dual UNION ALL --SELECT DATE '2020-02-01', '1001', '193456789' FROM dual UNION ALL --SELECT DATE '2020-02-01', '1001', '193456789' FROM dual UNION ALL --SELECT DATE '2020-02-01', '1001', '193456789' FROM dual UNION ALL --SELECT DATE '2020-02-01', '1001', '193456789' FROM dual UNION ALL SELECT DATE '2020-04-01', '1001', '133456789' FROM dual ) SELECT empno , TO_CHAR(dt, 'yyyy-q') yq , TO_CHAR(dt, 'yyyy-mm') ym , COUNT(*) cnt , LEAST(GREATEST( SUM(COUNT(*)) OVER(PARTITION BY empno, TO_CHAR(dt, 'yyyy-q') ORDER BY TO_CHAR(dt, 'yyyy-mm')) - 10 , 0), COUNT(*)) over_cnt FROM t GROUP BY empno , TO_CHAR(dt, 'yyyy-q') , TO_CHAR(dt, 'yyyy-mm') ;