Venkat Govind

Venkat Govind

  • 922
  • 568
  • 67.5k

How to display monthly total count

Jun 18 2015 9:22 AM
 Hi To all.
I using Below query to count RuninngTotal its showing below result.
 
select CONVERT(CHAR(4), wf.CreatedDatecount, 100) + CONVERT(CHAR(4), wf.CreatedDatecount, 120) AS MonthYear,
(select COUNT(CONVERT(CHAR(4), CreatedDatecount, 100) + CONVERT(CHAR(4),CreatedDatecount, 120)) from mstr_workforce where
CreatedDatecount<=wf.CreatedDatecount) as RuninngTotal
from mstr_workforce as wf where 1=1 and DATEADD(D, 0, DATEDIFF(D, 0,wf.CreatedDatecount)) Between '01/01/2015' and '12/30/2015' group by wf.CreatedDatecount
 
 
 
MonthYear  RuninngTotal(Total Employee of Register Every month)
Jan 2015     15(Register 15 employees in Jan Month )
Feb 2015    26( New  Register 11 employees in Feb Month )(26=15+11)
Mar 2015    37( New Register 11 employees in Feb Mar )(37=26+11)
Apr 2015    39( New Register 2 employees in Feb Mar )(39=37+2)
May 2015   51( New Register 12 employees in Feb Mar )(51=39+12)
   
this result correct 
 
but i  Want Display  below Like
 
here After may month i am not any  added employees but want display after coming month total employees 51  when i select created between Between '01/01/2015' and '12/30/2015'   
 
MonthYear RuninngTotal(Total Employee of Register Every month)
Jan 2015     15(Register 15 employees in Jan Month )
Feb 2015     26( New Register 11 employees in Feb Month )(26=15+11)
Mar 2015    37( New Register 11 employees in Feb Mar )(37=26+11)
Apr 2015    39( New Register 2 employees in Feb Mar )(39=37+2)
May 2015    51( New Register 12 employees in Feb Mar )(51=39+12)
Jun 2015   51(jun there is no data add in table )
July 2015  51(July there is no data in table )
Aug 2015  51(Aug there is no data in table )
Sep 2015  51(Sep there is no data in table )
Oct 2015  51(Oct there is no data in table )
Nov 2015  51(Nov there is no data in table )
Dec 2015   51(Dec there is no data in table )
 
Please Help me, I struck with that part of work
 
thanks
venkat