Getting data grouped by weekday and hour

Question

I have a table:

CREATE TABLE BolusData (
    [BolusKey] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
    [BolusDate] [datetime2](7) NOT NULL,
    [BolusSource] [nvarchar](50) NULL,
    [BolusVolDelivered] [numeric](8, 4) NULL,
 CONSTRAINT [PK_BolusKey] PRIMARY KEY CLUSTERED ([BolusKey] asc)
)

This table has about 16,000 rows of data like these examples:

Insert Into BolusData (BolusDate, BolusSource, BolusVolDelivered) Values ('3/16/2019 13:22:44', 'BOLUS_WIZARD', 3);
Insert Into BolusData (BolusDate, BolusSource, BolusVolDelivered) Values ('3/16/2019 13:20:42', 'BOLUS_WIZARD', NULL);
Insert Into BolusData (BolusDate, BolusSource, BolusVolDelivered) Values ('3/16/2019 13:20:41', null, NULL);
Insert Into BolusData (BolusDate, BolusSource, BolusVolDelivered) Values ('3/16/2019 12:31:18', 'BOLUS_WIZARD', 5);
Insert Into BolusData (BolusDate, BolusSource, BolusVolDelivered) Values ('3/16/2019 12:27:58', 'BOLUS_WIZARD', NULL);
Insert Into BolusData (BolusDate, BolusSource, BolusVolDelivered) Values ('3/16/2019 12:27:57', null, NULL);
Insert Into BolusData (BolusDate, BolusSource, BolusVolDelivered) Values ('3/16/2019 11:00:02', 'CLOSED_LOOP_MICRO_BOLUS', 0.05);
Insert Into BolusData (BolusDate, BolusSource, BolusVolDelivered) Values ('3/16/2019 10:55:04', 'CLOSED_LOOP_MICRO_BOLUS', 0.08);
Insert Into BolusData (BolusDate, BolusSource, BolusVolDelivered) Values ('3/16/2019 10:50:04', 'CLOSED_LOOP_MICRO_BOLUS', 0.08);
Insert Into BolusData (BolusDate, BolusSource, BolusVolDelivered) Values ('3/16/2019 10:45:02', 'CLOSED_LOOP_MICRO_BOLUS', 0.05);
Insert Into BolusData (BolusDate, BolusSource, BolusVolDelivered) Values ('3/16/2019 10:40:40', 'CLOSED_LOOP_FOOD_BOLUS', 20);
Insert Into BolusData (BolusDate, BolusSource, BolusVolDelivered) Values ('3/16/2019 10:27:19', 'CLOSED_LOOP_FOOD_BOLUS', NULL);
Insert Into BolusData (BolusDate, BolusSource, BolusVolDelivered) Values ('3/16/2019 10:25:04', 'CLOSED_LOOP_MICRO_BOLUS', 0.08);
Insert Into BolusData (BolusDate, BolusSource, BolusVolDelivered) Values ('3/16/2019 10:20:04', 'CLOSED_LOOP_MICRO_BOLUS', 0.08);
Insert Into BolusData (BolusDate, BolusSource, BolusVolDelivered) Values ('3/16/2019 10:15:02', 'CLOSED_LOOP_MICRO_BOLUS', 0.05);
Insert Into BolusData (BolusDate, BolusSource, BolusVolDelivered) Values ('3/16/2019 10:10:04', 'CLOSED_LOOP_MICRO_BOLUS', 0.08);

I want to build a report from this data that gives me the sum of Bolus Volume Delivered each hour, averaged by weekday, when the Bolus Source is "CLOSED_LOOP_MICRO_BOLUS". So the results should look something like:

Hour   Sun     Mon       Tue       Wed       Thu       Fri       Sat
0     3.787     3.019     3.581     4.027     3.499     3.470     3.603 
1     3.033     2.959     3.596     3.066     3.740     3.408     2.468 
2     2.854     3.134     2.818     2.722     3.057     3.242     3.074 
3     2.370     2.730     2.836     2.468     2.559     3.143     3.312 
4     2.421     3.294     2.851     1.882     3.020     2.956     2.846 
5     2.767     3.375     2.766     2.000     2.635     2.803     2.630 
[snip]
20    3.170     4.236     4.561     4.426     4.784     4.411     3.458 
21    3.479     3.913     4.677     3.910     5.155     4.352     4.380 
22    3.212     3.156     3.687     3.667     3.858     4.154     3.901 
23    3.246     2.416     3.238     3.943     3.296     3.772     3.829

I came up with this as one way to get the data, but I'm sure there's a better way:

with bolus_cte as (
select   
cast(Format( BolusDate, 'yyyy-MM-dd') as date) dt, 
format(BolusDate, 'HH') hr,
sum(BolusVolDelivered) Bolus --bolusDate, BolusVolDelivered
  from diabetes.bolus
  where BolusSource = 'CLOSED_LOOP_MICRO_BOLUS' 
  group by cast(Format( BolusDate, 'yyyy-MM-dd') as date), format(BolusDate, 'HH')
  ) 
  select hr, sum(ab_Sun) SunBolus, sum(ab_Mon) MonBolus , sum(ab_Tue) TueBolus, 
 sum(ab_Wed) WedBolus, sum(ab_Thu) ThuBolus, sum(ab_Fri) FriBolus,
 sum(ab_Sat) SatBolus
   from (
  select hr, avg(bolus) ab_Sun, 0 ab_Mon, 0 ab_Tue, 0 ab_Wed, 0 ab_Thu, 0 ab_Fri, 0 ab_Sat
   from bolus_cte
   where format(dt, 'ddd') = 'Sun'
   group by hr
   union
   select hr, 0, avg(bolus), 0, 0, 0, 0, 0
   from bolus_cte
   where format(dt, 'ddd') = 'Mon'
   group by hr 
      union
   select hr, 0, 0, avg(bolus), 0, 0, 0, 0
   from bolus_cte
   where format(dt, 'ddd') = 'Tue'
   group by hr 
   union
   select hr, 0, 0, 0, avg(bolus), 0, 0, 0
   from bolus_cte
   where format(dt, 'ddd') = 'Wed'
   group by hr 
   union
   select hr, 0, 0, 0, 0, avg(bolus), 0, 0
   from bolus_cte
   where format(dt, 'ddd') = 'Thu'
   group by hr 
   union
   select hr,  0, 0, 0, 0, 0, avg(bolus), 0
   from bolus_cte
   where format(dt, 'ddd') = 'Fri'
   group by hr 
   union
   select hr,  0, 0, 0, 0, 0, 0, avg(bolus)
   from bolus_cte
   where format(dt, 'ddd') = 'Sat'
   group by hr 

   ) a
   group by a.hr
   order by 1

Answer 1

How about:

;WITH x AS 
(
  SELECT d     = DATEPART(WEEKDAY, BolusDate), 
         Hour  = DATEPART(HOUR, BolusDate), 
         av    = avg(BolusVolDelivered)
  FROM dbo.BolusData
  WHERE BolusSource = 'CLOSED_LOOP_MICRO_BOLUS'
  GROUP BY DATEPART(WEEKDAY, BolusDate), DATEPART(HOUR, BolusDate)
)
SELECT Hour, 
       Sun  = MAX(CASE d WHEN 1 THEN av END),
       Mon  = MAX(CASE d WHEN 2 THEN av END),
       Tue  = MAX(CASE d WHEN 3 THEN av END),
       Wed  = MAX(CASE d WHEN 4 THEN av END),
       Thu  = MAX(CASE d WHEN 5 THEN av END),
       Fri  = MAX(CASE d WHEN 6 THEN av END),
       Sat  = MAX(CASE d WHEN 7 THEN av END) 
FROM x
GROUP BY Hour
ORDER BY Hour;

If you don't like the MAX/CASE repetition, you can also do:

;WITH x AS 
(
  SELECT d     = DATEPART(WEEKDAY, BolusDate), 
         Hour  = DATEPART(HOUR, BolusDate), 
         av    = avg(BolusVolDelivered)
  FROM dbo.BolusData
  WHERE BolusSource = 'CLOSED_LOOP_MICRO_BOLUS'
  GROUP BY DATEPART(WEEKDAY, BolusDate), DATEPART(HOUR, BolusDate)
)
SELECT Hour,       Sun = [1], Mon = [2], Tue = [3], 
       Wed  = [4], Thu = [5], Fri = [6], Sat = [7] 
FROM x
PIVOT (MAX(av) FOR d IN ([1],[2],[3],[4],[5],[6],[7])) AS p
ORDER BY Hour;