TSQL Reporting - Summary of Days of the Week?

Question

I am writing a report that should display weekly receipts, grouped by location, with a separate column for each day of the week. This will ship through SSRS and SQL Server 2008 R2. The end result should look like this: (but for all 7 days of the week)

Location Monday Tuesday Building3 $100 $75 Building4 $25 $35 Building5 $105 $21 

I wrote the following T-SQL SELECT statement to get this data from my report table variable (@reporting). In order to then group the output by location to get the layout shown above.

Raw data lists the date, total amount and location of the receipt on one line. I want to rotate the data so that the day of the week is at the top. There should be a better way to do this than you see below. Any help?

  SELECT LocationKey.Location ,Sunday.Cost as Sunday ,Monday.Cost as Monday FROM ( SELECT DISTINCT Location FROM @reporting WHERE Location NOT IN ('Building01', 'Building02', '') ) AS LocationKey LEFT JOIN (SELECT sunday.Location ,sunday.Cost FROM @reporting as sunday (nolock) WHERE DATEPART(weekday,sunday.ReceiptDate)= 1 ) AS Sunday ON Sunday.Location = LocationKey.Location LEFT JOIN (SELECT Monday.Location, Monday.Cost FROM @reporting as Monday (nolock) WHERE DATEPART(weekday,Monday.ReceiptDate)= 2 ) AS Monday ON Monday.Location = LocationKey.Location 

Answer 1

Probably the easiest way to do this is to PIVOT on a DATENAME .

 SELECT location, [Saturday], [Sunday], [Monday], [Tuesday], [Wednesday], [Thursday], [Friday] FROM (SELECT COST, location, Datename(weekday, receiptdate) DAY FROM @reporting WHERE location NOT IN ( 'Building01', 'Building02', '' )) p PIVOT ( SUM (COST) FOR DAY IN ( [Saturday], [Sunday], [Monday], [Tuesday], [Wednesday], [Thursday], [Friday]) ) pvt 

See how this data.se query works

Another way is to use several independent connections, but not run subqueries. The key here is the Join offer.

 SELECT LocationKey.Location, SUM(Sunday.Cost) As [Sunday], SUM(Monday.Cost) As [Monday], SUM(Tuesday.Cost) As [Tuesday], SUM(Wednesday.Cost) As [Wednesday], SUM(Thursday.Cost) As [Thursday], SUM(Friday.Cost) As [Friday], SUM(Saturday.Cost) As [Saturday] FROM (SELECT DISTINCT Location FROM @reporting WHERE Location NOT IN ('Building01', 'Building02', '')) LocationKey LEFT JOIN @Reporting Sunday ON LocationKey.Location = Sunday.Location AND DATEPART(weekday,sunday.ReceiptDate)= 1 LEFT JOIN @Reporting Monday ON LocationKey.Location = Monday.Location AND DATEPART(weekday,Monday.ReceiptDate)= 2 LEFT JOIN @Reporting Tuesday ON LocationKey.Location = Tuesday.Location AND DATEPART(weekday,Tuesday.ReceiptDate)= 3 LEFT JOIN @Reporting Wednesday ON LocationKey.Location = Wednesday.Location AND DATEPART(weekday,Wednesday.ReceiptDate)= 4 LEFT JOIN @Reporting Thursday ON LocationKey.Location = Thursday.Location AND DATEPART(weekday,Thursday.ReceiptDate)= 5 LEFT JOIN @Reporting Friday ON LocationKey.Location = Friday.Location AND DATEPART(weekday,Friday.ReceiptDate)= 6 LEFT JOIN @Reporting Saturday ON LocationKey.Location = Saturday.Location AND DATEPART(weekday,Saturday.ReceiptDate)= 7 

It should be noted that you must either call SET DATEFIRST or use the @@DATEFIRST to protect your request from potential. The default settings change and break your request when you use DATEPART(weekday..

Answer 2

This PIVOT request should get what you want:

 select Location, Cost_D1, Cost_D2, Cost_D3, Cost_D4, Cost_D5, Cost_D6, Cost_D7 from ( select Location, 'Cost_D' + convert(varchar,datepart(weekday,ReceiptDate)) as FieldName, Cost from @reporting ) s pivot ( sum(Cost) for FieldName in (Cost_D1, Cost_D2, Cost_D3, Cost_D4, Cost_D5, Cost_D6, Cost_D7) ) p