SELECT AA.companyName ,AA.M1 ,AA.M2 ,AA.M3 ,AA.M4 ,AA.M5 ,AA.M6 ,AA.M7 ,AA.M8 ,AA.M9 ,AA.M10 ,AA.M11 ,AA.M12 ,SUM(M1+M2+M3+M4+M5+M6+M7+M8+M9+M10+M11+M12) AS totalCnt FROM ( SELECT CASE WHEN A.BIZ_CD= '10' THEN 'A' WHEN A.BIZ_CD= '20' THEN 'B' END AS companyName ,COUNT(CASE WHEN mm = '01' THEN 1 END) m1 , COUNT(CASE WHEN mm = '02' THEN 1 END) m2 , COUNT(CASE WHEN mm = '03' THEN 1 END) m3 , COUNT(CASE WHEN mm = '04' THEN 1 END) m4 , COUNT(CASE WHEN mm = '05' THEN 1 END) m5 , COUNT(CASE WHEN mm = '06' THEN 1 END) m6 , COUNT(CASE WHEN mm = '07' THEN 1 END) m7 , COUNT(CASE WHEN mm = '08' THEN 1 END) m8 , COUNT(CASE WHEN mm = '09' THEN 1 END) m9 , COUNT(CASE WHEN mm = '10' THEN 1 END) m10 , COUNT(CASE WHEN mm = '11' THEN 1 END) m11 , COUNT(CASE WHEN mm = '12' THEN 1 END) m12 FROM (SELECT us.BIZ_CD , DATE_FORMAT(item.regist_date - INTERVAL '15' DAY + INTERVAL 1 MONTH, '%m') mm FROM ITEM item INNER JOIN USER us ON item.REGISTER_ID = us.USER_ID LEFT OUTER JOIN ( SELECT A.ITEM_LINK_ID, STATUS AS TODO_STATUS, fncGetCodeName(STATUS) AS TODO_STATUS_NAME, C.GROUP_ID AS TODO_GROUP_ID, C.GROUP_NAME AS TODO_GROUP_NAME FROM ( SELECT * FROM BM_IM_IMPORTANT WHERE TYPE = '101200267905' AND IS_DELETE = '0' ) B INNER join BM_ITEM_LINK A ON A.ITEM_ID = B.IMPORTANT_ID AND A.ITEM_TYPE='TD' AND A.ITEM_LINK_TYPE='PR' INNER JOIN GROUP C ON B.MANAGE_GROUP_ID = C.GROUP_ID ) bms ON bms.ITEM_LINK_ID = item.PROP_ID WHERE 1=1 AND item.REGIST_DATE >= '2017-12-16' AND item.REGIST_DATE < '2018-12-15' ) A GROUP BY companyName with rollup ) AA GROUP BY companyName ,m1 ,m2 ,m3,m4,m5,m6,m7,m8,m9,m10,m11,m12 ORDER BY companyName desc
뽑고 싶은형식은 예를 들어 2017-12-16 부터 2018-01-15 일까지가 1월 데이터 카운트
2018-01-16 부터 2018-02-15 일 까지가 2월 데이터 카운트 해서 1년치를 뽑아야 합니다.
근데 , DATE_FORMAT(item.regist_date - INTERVAL '15' DAY + INTERVAL 1 MONTH, '%m') mm 요렇게 뽑아오면 카운트가 1월달 카운트가 맞지 않아서
요부분을 바꿔야 할꺼같은데 어떤 형식으로 바꿔야 할지 고민을 하다가
,case when item.REGIST_DATE between '2017-12-16' and '2018-01-15' then '01' when item.REGIST_DATE between '2018-01-16' and '2018-02-15' then '02' when item.REGIST_DATE between '2018-02-16' and '2018-03-15' then '03' when item.REGIST_DATE between '2018-03-16' and '2018-04-15' then '04' when item.REGIST_DATE between '2018-04-16' and '2018-05-15' then '05' when item.REGIST_DATE between '2018-05-16' and '2018-06-15' then '06' when item.REGIST_DATE between '2018-06-16' and '2018-07-15' then '07' when item.REGIST_DATE between '2018-07-16' and '2018-08-15' then '08' when item.REGIST_DATE between '2018-08-16' and '2018-09-15' then '09' when item.REGIST_DATE between '2018-09-16' and '2018-10-15' then '10' when item.REGIST_DATE between '2018-10-16' and '2018-11-15' then '11' when item.REGIST_DATE between '2018-11-16' and '2018-12-15' then '12'
요런식으로 날자를 박아서 가져오긴 했는데 그래도 중간중간 빠지는 데이터가 있는거 같습니다 ㅠ
어떤식으로 가져와야 정확하게 될까 싶어 질문드립니다.
case when item.REGIST_DATE between '2017-12-16' and '2018-01-15' then '01' when item.REGIST_DATE between '2018-01-16' and '2018-02-15' then '02' when item.REGIST_DATE between '2018-02-16' and '2018-03-15' then '03' when item.REGIST_DATE between '2018-03-16' and '2018-04-15' then '04' when item.REGIST_DATE between '2018-04-16' and '2018-05-15' then '05' when item.REGIST_DATE between '2018-05-16' and '2018-06-15' then '06' when item.REGIST_DATE between '2018-06-16' and '2018-07-15' then '07' when item.REGIST_DATE between '2018-07-16' and '2018-08-15' then '08' when item.REGIST_DATE between '2018-08-16' and '2018-09-15' then '09' when item.REGIST_DATE between '2018-09-16' and '2018-10-15' then '10' when item.REGIST_DATE between '2018-10-16' and '2018-11-15' then '11' when item.REGIST_DATE between '2018-11-16' and '2018-12-15' then '12' 이런식으로 하시면 SQL을 연도마다 다르게 적어주셔야합니다. WHERE 조건에 연도를 넣으시고 CASE WHEN ...조건에 월만 가지고 하셔야 합니다.
맨처음 올려주신 쿼리 쓰시면 될것같습니다.