More fun with pivots!

[wbodger]

If this query

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(study_site_id) 
                    from TBLSTUDYDOC
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
        
        	 
	SET @query =
	'SELECT weeknum,c.weekstart,c.weekend,'+@cols+' FROM vwCalendar C left join
	(
	    SELECT cast(DATEADD(dd, -(DATEPART(dw, STUDY_CONSENT_DATE)-1), STUDY_CONSENT_DATE) as DATE)  [WeekStart],
		cast(DATEADD(dd, 7-(DATEPART(dw, STUDY_CONSENT_DATE)), STUDY_CONSENT_DATE) as DATE)  [WeekEnd],
		STUDY_SITE_ID
  FROM [dbEDRN320].[dbo].[TBLSTUDYDOC]
  where STUDY_PROTOCOL_VISIT_CODE=1
  and DELETEFLAG=0 and ENTRYFLAG=1
	)t
	PIVOT (count(study_site_id) FOR study_site_id
	IN ('+@cols+') ) pvt on C.weekstart=pvt.weekstart
	where C.weekstart<getdate()
	order by C.weeknum'
	 
	EXECUTE (@query)

gives me this

1	2011-09-25	2011-10-01	3	1	0	0	2	0	0	0
2	2011-10-02	2011-10-08	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
3	2011-10-09	2011-10-15	0	2	0	0	0	0	0	0
4	2011-10-16	2011-10-22	0	1	0	0	0	0	0	0
5	2011-10-23	2011-10-29	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
6	2011-10-30	2011-11-05	2	3	0	0	0	0	0	0
7	2011-11-06	2011-11-12	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
8	2011-11-13	2011-11-19	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
9	2011-11-20	2011-11-26	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
10	2011-11-27	2011-12-03	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
11	2011-12-04	2011-12-10	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
12	2011-12-11	2011-12-17	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
13	2011-12-18	2011-12-24	1	1	0	0	0	0	0	0
14	2011-12-25	2011-12-31	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
15	2012-01-01	2012-01-07	0	1	0	0	0	0	0	0
16	2012-01-08	2012-01-14	0	1	0	0	0	0	0	0
17	2012-01-15	2012-01-21	0	1	0	0	0	0	0	0
18	2012-01-22	2012-01-28	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
19	2012-01-29	2012-02-04	1	2	0	0	0	0	0	0
20	2012-02-05	2012-02-11	0	1	0	0	0	0	0	0
21	2012-02-12	2012-02-18	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
22	2012-02-19	2012-02-25	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
23	2012-02-26	2012-03-03	0	1	0	0	0	0	0	0
24	2012-03-04	2012-03-10	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
25	2012-03-11	2012-03-17	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
26	2012-03-18	2012-03-24	0	1	0	0	0	0	0	0
27	2012-03-25	2012-03-31	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
28	2012-04-01	2012-04-07	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
29	2012-04-08	2012-04-14	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
30	2012-04-15	2012-04-21	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
31	2012-04-22	2012-04-28	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
32	2012-04-29	2012-05-05	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
33	2012-05-06	2012-05-12	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
34	2012-05-13	2012-05-19	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
35	2012-05-20	2012-05-26	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
36	2012-05-27	2012-06-02	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
37	2012-06-03	2012-06-09	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
38	2012-06-10	2012-06-16	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
39	2012-06-17	2012-06-23	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
40	2012-06-24	2012-06-30	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
41	2012-07-01	2012-07-07	1	0	0	0	0	0	0	0
42	2012-07-08	2012-07-14	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
43	2012-07-15	2012-07-21	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
44	2012-07-22	2012-07-28	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
45	2012-07-29	2012-08-04	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
46	2012-08-05	2012-08-11	0	0	0	0	0	0	1	0
47	2012-08-12	2012-08-18	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
48	2012-08-19	2012-08-25	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
49	2012-08-26	2012-09-01	1	6	0	0	0	0	1	0

and I want to get totals by week (the number on the far left) and by site (those are the 661,662 etc.) and a running cumulative total, how can I modify my query without disturbing the delicate balance at which I have arrived? I suppose I could do the totals in code, but would much prefer to handle them in the SQL. I have the above in a stored procedure if that is of any help.

Thanks for your time!
Willie

 

[timfoster]

You're going to over complicate your query horribly. You can use the cube and rollup functions but as I said this will make it nasty! Dynamic sql is a pain in the proverbial at the best of times. When you start adding dynamic pivots into the mix it's probably better to look at alternative methods.

If you're tied to that structure, you might be better dynamically creating the table and then inserting the results of your dynamic pivot. After that insnert a total row and return the table.

It's not pretty, but then neither is dyanmic pivotting.

 

[wbodger]

I currently have this down to this code

with sitesInWeeks as
(
	select  
		cast(DATEADD(dd, -(DATEPART(dw, STUDY_CONSENT_DATE)-1), STUDY_CONSENT_DATE) as date) as weekStart ,
		cast(DATEADD(dd, 7-(DATEPART(dw, STUDY_CONSENT_DATE)), STUDY_CONSENT_DATE) as date)  as weekEnd ,
		study_site_id
	from db.dbo.tblStudyDoc
	where STUDY_PROTOCOL_VISIT_CODE = 1
),
siteCounts as
(
	select weekStart , weekEnd , count(study_site_id) as siteCountsByWeek
	from sitesInWeeks
	group by weekStart , weekEnd 
),
siteTotals as
(
SELECT 10000000 as weeknum,' ' as weekstart,' ' as weekend
	,(select count(661) from sitesInWeeks where STUDY_SITE_ID='661') as '661'
	,(select count(662) from sitesInWeeks where STUDY_SITE_ID='662') as '662'
	,(select count(663) from sitesInWeeks where STUDY_SITE_ID='663') as '663'
	,(select count(664) from sitesInWeeks where STUDY_SITE_ID='664') as '664'
	,(select count(665) from sitesInWeeks where STUDY_SITE_ID='665') as '665'
	,(select count(666) from sitesInWeeks where STUDY_SITE_ID='666') as '666'
	,(select count(667) from sitesInWeeks where STUDY_SITE_ID='667') as '667'
	,(select count(668) from sitesInWeeks where STUDY_SITE_ID='668') as '668'
	,(select count(671) from sitesInWeeks where STUDY_SITE_ID='671') as '671'
	,(select count(672) from sitesInWeeks where STUDY_SITE_ID='672') as '672'
	,(select count(676) from sitesInWeeks where STUDY_SITE_ID='676') as '676'
	,(select count(677) from sitesInWeeks where STUDY_SITE_ID='677') as '677'
	,(select count(703) from sitesInWeeks where STUDY_SITE_ID='703') as '703'
	,(select count(704) from sitesInWeeks where STUDY_SITE_ID='704') as '704'
	,(select count(705) from sitesInWeeks where STUDY_SITE_ID='705') as '705'
	,(select count(706) from sitesInWeeks where STUDY_SITE_ID='706') as '706'
	,(select count(707) from sitesInWeeks where STUDY_SITE_ID='707') as '707'
	,(select count(708) from sitesInWeeks where STUDY_SITE_ID='708') as '708'
	,(select count(710) from sitesInWeeks where STUDY_SITE_ID='710') as '710'
	,(select count(714) from sitesInWeeks where STUDY_SITE_ID='714') as '714'
	,(select count(715) from sitesInWeeks where STUDY_SITE_ID='715') as '715'
	,(select count(716) from sitesInWeeks where STUDY_SITE_ID='716') as '716'
	,(select count(717) from sitesInWeeks where STUDY_SITE_ID='717') as '717'
	,(select count(718) from sitesInWeeks where STUDY_SITE_ID='718') as '718'
	,(select count(719) from sitesInWeeks where STUDY_SITE_ID='719') as '719'
	,(select count(720) from sitesInWeeks where STUDY_SITE_ID='720') as '720'
	,(select count(721) from sitesInWeeks where STUDY_SITE_ID='721') as '721'
	,(select count(722) from sitesInWeeks where STUDY_SITE_ID='722') as '722'
	,(select count(723) from sitesInWeeks where STUDY_SITE_ID='723') as '723'
	,(select count(724) from sitesInWeeks where STUDY_SITE_ID='724') as '724'
	,(select count(725) from sitesInWeeks where STUDY_SITE_ID='725') as '725'
	,(select count(study_site_ID) from sitesInWeeks) as siteCountsByWeek
	,(select count(study_site_ID) from sitesInWeeks) as runningtotal
FROM sitesInWeeks
)

select vwCalendar.weeknum , 
       vwCalendar.weekstart , 
       vwCalendar.weekend , 
       [661], [662], [663], [664], [665], [666], [667], [668], [671], [672], [676], [677], [703], [704], [705], [706], [707], [708], [710], [714], [715], [716], [717], [718], [719], [720], [721], [722], [723], [724], [725], 
       siteCountsByWeek, 
	   (select SUM(cs2.siteCountsByWeek) from siteCounts cs2 where cs2.weekStart<=siteCounts.weekStart) as runningtotal
 
from db.dbo.vwCalendar 

     left join
     
     sitesInWeeks 
    
     pivot (count(study_site_id) 
           for study_site_id in ([661], [662], [663], [664], [665], [666], [667], [668], [671], [672], [676], [677], [703], [704], [705], [706], [707], [708], [710], [714], [715], [716], [717], [718], [719], [720], [721], [722], [723], [724], [725]) 
           ) as pvt on vwCalendar.weekstart = pvt.weekstart
           
     left join 
     
     siteCounts on vwCalendar.weekStart = siteCounts.weekStart and        
                   vwCalendar.weekEnd   = siteCounts.weekEnd  

where vwCalendar.weekstart < getdate()
UNION
select * from siteTotals

I dislike all of the hardcoding that I ended up doing, but to get the output I was going for I didn't see another way.