Pivot 12 Months’ User Counts


https://social.msdn.microsoft.com/Forums/en-US/bb4c1214-cbbf-42e7-8626-145c0a8bfef4/sql-server-count-1-for-all-active-months-during-reporting-period?forum=transactsql

  
 
create table #base
(RecID int, FromDate
date , ToDate date
)
insert into #base
values (17669977,'5/13/2016','11/4/2016')
insert into #base
values (17683279, '4/19/2016',
'10/4/2016')
insert into #base
values (17693200, '5/2/2016',
'11/4/2016')
;with mycte0 as (
Select RecID, Datename(Month,Dateadd(month,-n+1,getdate())) mth
, Dateadd(month,-n+1,getdate())dt,n 
From #base, (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12) ) d(n)
)
 
 

, mycte1 as ( 
 Select RecID,  Dateadd(month,n-1,FromDate) dt, Datename(Month,  Dateadd(month,n-1,FromDate)) mth from #base
Cross apply (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12) ) d(n)
WHERE 
--RecID=17669977  And 
Dateadd(month,n-1,FromDate) Between FromDate and EOMONTH(ToDate)

)


select
-- m0.RecID ,
Sum(Case when m0.mth='September' and  m1.mth is not null then 1 else 0 End)  'September'
,Sum(Case when m0.mth='August' and  m1.mth is not null then 1 else 0 End)  'August'
,Sum(Case when m0.mth='July' and  m1.mth is not null then 1 else 0 End)  'July'
,Sum(Case when m0.mth='June' and  m1.mth is not null then 1 else 0 End)  'June'
,Sum(Case when m0.mth='May' and  m1.mth is not null then 1 else 0 End)  'May'
,Sum(Case when m0.mth='April' and  m1.mth is not null then 1 else 0 End)  'April'
,Sum(Case when m0.mth='March' and  m1.mth is not null then 1 else 0 End)  'March'
,Sum(Case when m0.mth='Feburary' and  m1.mth is not null then 1 else 0 End)  'Feburary'
,Sum(Case when m0.mth='January' and  m1.mth is not null then 1 else 0 End)  'January'
,Sum(Case when m0.mth='December' and  m1.mth is not null then 1 else 0 End)  'December'
,Sum(Case when m0.mth='November' and  m1.mth is not null then 1 else 0 End)  'November'
,Sum(Case when m0.mth='October' and  m1.mth is not null then 1 else 0 End)  'October'
 



 from mycte0 m0 left join mycte1 m1 on m0.RecID=m1.RecID and m0.mth=m1.mth
 --Group by  m0.RecID



drop table #base

  
   
 
 

 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s