WITH T1 AS ( SELECT 1 AS id, '20121001' AS startdate, '' AS enddate, 'A' AS cls FROM DUAL UNION ALL SELECT 2, '20121003', '', 'B' FROM DUAL UNION ALL SELECT 3, '20120920', '20121002', 'B' FROM DUAL UNION ALL SELECT 4, '20121001', '20121005', 'C' FROM DUAL UNION ALL SELECT 5, '20120901', '20120930','A' FROM DUAL ), T2 AS ( SELECT TO_DATE('2012-10-'||LPAD(LEVEL, 2, '0')) AS dday FROM DUAL CONNECT BY LEVEL <= 31 ) SELECT TO_CHAR(dday, 'YYYY-MM-DD') AS dday, COUNT(id) AS user_count FROM T2 LEFT OUTER JOIN T1 ON dday >= startdate AND dday <= enddate GROUP BY ROLLUP(dday) ORDER BY dday nulls first
-- 아래 쿼리중 '20120101' 은 원하는 달 01 날짜 입니다. with t(id,startdate,enddate,class) as ( select '1','20121001',null,'A' from dual union all select '2','20121003',null,'B' from dual union all select '3','20120920','20121002','B' from dual union all select '4','20121001','20121005','C' from dual union all select '5','20120901','20120930','A' from dual ) select b.dt, t.class, count(*) cnt from ( select to_char(to_date('20121001','yyyymmdd')+(level-1),'yyyymmdd') dt from dual connect by level <= to_number(to_char(last_day(to_date('20121001','yyyymmdd')),'dd')) ) b left outer join t on t.startdate <= b.dt and nvl(enddate,'29991231') >= b.dt group by ROLLUP((b.dt, t.class)) order by b.dt, t.class
-- 아래 쿼리중 '20120101' 은 원하는 달 01 날짜 입니다. -- 클래스별 데이터가 없으면 0 row 도 추가한 쿼리 입니다. with t(id,startdate,enddate,class) as ( select '1','20121001',null,'A' from dual union all select '2','20121003',null,'B' from dual union all select '3','20120920','20121002','B' from dual union all select '4','20121001','20121005','C' from dual union all select '5','20120901','20120930','A' from dual ) select b.dt, t2.class, count(t.class) cnt from ( select to_char(to_date('20121001','yyyymmdd')+(level-1),'yyyymmdd') dt from dual connect by level <= to_number(to_char(last_day(to_date('20121001','yyyymmdd')),'dd')) ) b inner join (select class from t group by class) t2 on 1=1 left outer join t on t.startdate <= b.dt and nvl(enddate,'29991231') >= b.dt and t2.class = t.class group by ROLLUP((b.dt, t2.class)) order by b.dt, t2.class