Count entries per month but total previous months SQL

Question

I need to get the total number of accounts per month but in the next month it should include the previous total. There are 2 column in the table one for AccNO and another for date. We count the totals per month group by month and date.

accNO           HO_DATE
---------------------------
TN128757    2013-07-21

TN126822    2013-05-16

TN130058    2013-10-11

TN130375    2013-10-30

TN128396    2013-06-20

TN129800    2013-10-22

TN127399    2013-08-25

so in the end the totals would be

2013/05 = 1

2013/06 = 2

2013/07 = 3

2013/08 = 4

2013/09 = 4

2013/10 = 7

so how would I group by or how would this be included in the where clause. I am using MSSQL 2012

thanks

Answer 1

I am using the data you provided into #tbl, then make a #tbl1 with count of each Month and year. After that I join #tbl1 with #tbl itself with condition like less than and equal to Year and Month.

select * into #tbl from 
(
select 'TN128757' accNO, CAST('2013-07-21' AS date) HO_DATE
union all select 'TN126822', cast('2013-05-16' as date)
union all select 'TN130058', cast('2013-10-11' as date)
union all select 'TN130375', cast('2013-10-30' as date)
union all select 'TN128396', cast('2013-06-20' as date)
union all select 'TN129800', cast('2013-10-22' as date)
union all select 'TN127399', cast('2013-08-25' as date)
) A

select * into #tbl1 from (
select COUNT(*) Cnt, CAST(DATEPART(Year,HO_DATE) as varchar) + '-' + CAST(DATEPART(MONTH,HO_DATE) as varchar) MONTHYEAR, DATEPART(Year,HO_DATE) YEAR ,DATEPART(MONTH,HO_DATE) MONTH
from #tbl 
group by DATEPART(Year,HO_DATE),DATEPART(MONTH,HO_DATE), CAST(DATEPART(Year,HO_DATE) as varchar) + '-' + CAST(DATEPART(MONTH,HO_DATE) as varchar)
) A

select * from #tbl1

select  A.MONTHYEAR, A.YEAR, A.MONTH, SUM(B.Cnt) from #tbl1 A
inner join #tbl1 B ON A.YEAR>=B.YEAR and A.MONTH>=B.MONTH
GROUP BY A.MONTHYEAR, A.YEAR,A.MONTH
order by A.YEAR,A.MONTH

drop table #tbl;
drop table #tbl1;

Hope this works for you. Give the table name accordingly and let me know if you have any issues.

Cheers :) ......