mssql 날짜별 누적 가입 자 수 계산
-- 날자별 가입 자 수
select convert(char(10), tb1.reg_dttm, 120) dd, count(*) as cnt
from t1_user as tb1
group by convert(char(10), tb1.reg_dttm, 120)
----------------------------------------------------
2015-08-25 7
2015-08-26 11
2015-08-27 22
-- 날짜별 누적 가입 자 수
select tb2.dd, tb2.cnt, sum(tb1.cnt) as totcnt
from (
select convert(char(10), tb1.reg_dttm, 120) dd, count(*) as cnt
from t1_user as tb1
group by convert(char(10), tb1.reg_dttm, 120)
) as tb1
join
(
select convert(char(10), tb1.reg_dttm, 120) dd, count(*) as cnt
from t1_user as tb1
group by convert(char(10), tb1.reg_dttm, 120)
)
as tb2
on tb1.dd <= tb2.dd
group by tb2.dd, tb2.cnt
------------------------------------------------
2015-08-25 7 7
2015-08-26 11 18
2015-08-27 22 40
