0

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
3
3

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;
2
  • This gives the average over each hour, but I need to get the sum over the course of each hour, so if there are 10 rows within an hour of one day, each of 0.25 bolus delivered, then that hour's data is 2.50. then average all of those hours, splitting on hour and weekday.
    – CaM
    Mar 18 '19 at 17:49
  • Sorry, don't follow the word problem. I'll refer you to the comment above about creating a real MCVE. If we can't reproduce the answer you want from the data you've provided, you either need to fix the answer or fix the data. Mar 18 '19 at 19:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.