CODEWITHSUNDEEP
sqlsql-serversql-server-2008pivot
good-to-know
good-to-know

Reputation: 742

SQL results grouped by Weeks, Day

I have a table like below:

Date        ServingTime   MeasuredValue
----------------------------------------
01-11-16    Breakfast     45
01-11-16    Lunch         50
01-11-16    Dinner        60
02-11-16    Breakfast     23
02-11-16    Lunch         45
02-11-16    Dinner        83
…       
…       
30-11-16    Breakfast     56
30-11-16    Lunch         16
30-11-16    Dinner        34

And Query like below:

DECLARE @DatePeriod date = '2016-11-30'

SELECT
  ServingTime,
  ISNULL([1], 0) AS 'Week 1',
  ISNULL([2], 0) AS 'Week 2',
  ISNULL([3], 0) AS 'Week 3',
  ISNULL([4], 0) AS 'Week 4',
  ISNULL([5], 0) AS 'Week 5'

FROM (SELECT
  ServingTime,
  DATEDIFF(WEEK, DATEADD(MONTH, DATEDIFF(MONTH, 0, Date), 0), Date) + 1 AS [Weeks],
  MeasuredValue AS 'Sale'

FROM HR.FoodCollectionEntry
-- Only get rows where the date is the same as the DatePeriod
-- i.e DatePeriod is 30th May 2011 then only the weeks of May will be calculated
WHERE DATEPART(MONTH, Date) = DATEPART(MONTH, @DatePeriod)) p
PIVOT (SUM(Sale) FOR Weeks IN ([1], [2], [3], [4], [5], [6])) AS pv

This query output like below:

ServingTime    Week 1    Week 2    Week 3    Week 4     Week 5
---------------------------------------------------------------
Breakfast      412       590       510       456        200
Dinner         329       525       529       529        321
Lunch          371       529       542       480        233

But I would to bring Day also in the output to compare the sales of Sunday in each weeks. How to query to bring output like below?

Day        ServingTime  Week 1  Week 2  Week 3  Week 4  Week 5
---------------------------------------------------------------
Sunday     Breakfast    412     590     510     456     200
Sunday     Dinner       329     525     529     529     321
Sunday     Lunch        371     529     542     480     233
Monday     Breakfast    412     590     510     456     200
Monday     Dinner       329     525     529     529     321
Monday     Lunch        371     529     542     480     233
…                       
…                       
Saturday   Breakfast    412     590     510     456     200
Saturday   Dinner       329     525     529     529     321
Saturday   Lunch        371     529     542     480     233

Upvotes: 1

Views: 50

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Just use conditional aggregation:

select datename(weekday, fce.date) as dayOfWeek, servingTime,
       sum(case when weeks = 1 then sale else 0 end) as Week_1,
       sum(case when weeks = 2 then sale else 0 end) as Week_2,
       sum(case when weeks = 3 then sale else 0 end) as Week_3,
       sum(case when weeks = 4 then sale else 0 end) as Week_4,
       sum(case when weeks = 5 then sale else 0 end) as Week_5
from (select fce.*,
             datediff(week, dateadd(month, datediff(month, 0, Date), 0), Date) + 1 AS Weeks
      from HR.FoodCollectionEntry fce
     ) fce
where month(Date) = month(@DatePeriod) and
      year(Date) = year(@DatePeriod)  -- I assume you want this too
      group by datename(weekday, fce.date) as dayOfWeek, servingTime
order by max(datepart(weekday, fce.date)

Upvotes: 2

Related Questions