question

W Cody avatar image
W Cody asked

Column headings in crosstabs report

I am using SQL Server 2005 and do not understand how I can produce column heading names for a particular crosstabs report.

My data table is simple, just two columns, EventID int (Identity(1,1)), and EventDate (datetime). I want to produce a crosstab of the count of events per week and per day of the week for the previous 8 weeks when the first day of the week is selected by the end-user. For instance, one end user may want to see the event counts from Monday through Sunday, whereas a different user wants the report from Wednesday through the following Tuesday. The following SQL generates the correct cell and marginal totals assuming the first day of the report is 5/26/2010 (a Wednesday):

DECLARE @StartDate datetime SELECT @StartDate = '2010-05-26'

SELECT (CASE WHEN z.WeekName IS NULL THEN 'Sum' ELSE z.WeekName END) as WeekName ,COUNT(CASE WHEN DayWithinWeek=1 THEN 1 ELSE NULL END) as D1 ,COUNT(CASE WHEN DayWithinWeek=2 THEN 1 ELSE NULL END) as D2 ,COUNT(CASE WHEN DayWithinWeek=3 THEN 1 ELSE NULL END) as D3 ,COUNT(CASE WHEN DayWithinWeek=4 THEN 1 ELSE NULL END) as D4 ,COUNT(CASE WHEN DayWithinWeek=5 THEN 1 ELSE NULL END) as D5 ,COUNT(CASE WHEN DayWithinWeek=6 THEN 1 ELSE NULL END) as D6 ,COUNT(CASE WHEN DayWithinWeek=7 THEN 1 ELSE NULL END) as D7 ,COUNT(EventID) as [Total] FROM ( SELECT e.EventID ,DATEADD(wk,(DATEDIFF(d,@StartDate,e.DueDate)/7),@StartDate) as WeekStartDate ,DATEADD(d,6,DATEADD(wk,(DATEDIFF(d,@StartDate,e.DueDate)/7),@StartDate)) as WeekEndDate ,CONVERT(varchar(10),DATEADD(wk,(DATEDIFF(d,@StartDate,e.DueDate)/7),@StartDate),111) as WeekStartName ,CONVERT(varchar(10),DATEADD(d,6,DATEADD(wk,(DATEDIFF(d,@StartDate,e.DueDate)/7),@StartDate)),111) as WeekendName ,(CONVERT(varchar(10),DATEADD(wk,(DATEDIFF(d,@StartDate,e.DueDate)/7),@StartDate),111) + '-' + CONVERT(varchar(10),DATEADD(d,6,DATEADD(wk,(DATEDIFF(d,@StartDate,e.DueDate)/7),@StartDate)),111)) as WeekName ,((DATEDIFF(d,@StartDate,e.DueDate)/7)+1) as WeekNumber ,((DATEDIFF(d,@StartDate,e.DueDate) % 7)+1) as DayWithinWeek ,SUBSTRING(DATENAME(weekday, e.DueDate), 1,3) as Dy FROM Tally as t LEFT JOIN [Event] as e ON t.NumID=DATEDIFF(d,@StartDate,e.DueDate) WHERE t.NumID <= DATEDIFF(d,@StartDate,e.DueDate) ) as z

GROUP BY z.WeekName WITH ROLLUP ORDER BY z.WeekName DESC

What I can't figure out is how to produce column headings of "Wed", "Thu" "Fri", etc. and starting with the correct day, instead of D1, D2, D3....

Any help is appreciated.

sql-server-2005
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Squirrel 1 avatar image
Squirrel 1 answered

you will need to use Dynamic SQL to do it

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

W Cody avatar image
W Cody answered

Yes, I assumed that dynamic SQL would be required, and I understand dynamic SQL in general. But I'm stuck on this particular problem...producing the list of weekday names in correct order when the first day can be any of the seven days. Also, using DATEFIRST to set the first day of the week is not an option since the application must allow any user to set his preferred first day every time he runs the query.

At the moment, I'm resolving the weekday name at the presentation layer. Just thought that someone may have produced a dynamic SQL snippet for this particular need...seems like the need would be pretty common.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.