compile sql server online

Language: Layout:

--Title of this code
--Batches are separated by 'go'

select @@version as 'sql server version'

set dateformat DMY
declare @Account Table (AccountNo int, 
                        Status char(10),
                        DateComplete date,
                        DateDue date,
                        [STATE] CHAR(1)
                        )
                        
insert into  @Account values 
(1147331,'OPEN','18/02/2011','10/03/2011','R'),
(1146787,'OPEN','07/04/2011','05/04/2011','R'),
(16511  ,'OPEN','29/09/2003','29/09/2003','R'),
(22571  ,'OPEN','15/10/2003','15/10/2003','R'),
(2852403,'OPEN','10/11/2014','10/11/2014','R'),
(2851890,'OPEN','17/11/2014','17/11/2014','R'),
(1147331,'CLOSED','24/06/2011','27/06/2011','R'),
(1146787,'CLOSED','14/06/2011','14/06/2011','R'),
(2852403,'CLOSED','11/08/2015','11/08/2015','F'),
(2851890,'CLOSED','18/08/2015','18/08/2015','F');

select CASE [STATE] WHEN 'F' THEN DateDue 
   WHEN 'R' THEN DateComplete END AS TransDate         
 from  @Account
 where Status = 'OPEN'
 
 
;with CTE as -- Get all dates with month year combo
( select CASE [STATE] WHEN 'F' THEN DateDue 
                      WHEN 'R' THEN DateComplete END AS TransDate         
 from  @Account
 where Status = 'OPEN'  
 )  
select  DATENAME(mm,T1.TransDate) +'-'+
        CAST (YEAR(T1.TransDate) AS VARCHAR(20)) AS Month,
        count(distinct T2.TransDate) as runsum
from CTE as T2
cross join CTE T1  
where 1= case when  DATENAME(mm,T1.TransDate) +'-'+ 
                    CAST (YEAR(T1.TransDate) AS VARCHAR(20)) =
                    DATENAME(mm,T2.TransDate) +'-'+ 
                    CAST (YEAR(T2.TransDate) AS VARCHAR(20)) 
                    and  T2.TransDate <  T1.TransDate  then 1
              when T2.TransDate <=  T1.TransDate then 1
         end
group by DATENAME(mm,T1.TransDate) +'-'+ 
         CAST (YEAR(T1.TransDate) AS VARCHAR(20))
order by runsum asc

View schema
Execution time: 0.03 sec, rows selected: 12, rows affected: 10, absolute service time: 0.16 sec