clear markdown compare help best fiddles feedback dbanow.uk
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2591646 fiddles created (45705 in the last week).

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))
 hidden batch(es)


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);
16 rows affected
 hidden batch(es)


select * from bolusdata
BolusKey BolusDate BolusSource BolusVolDelivered
1 16/03/2019 13:22:44 BOLUS_WIZARD 3.0000
2 16/03/2019 13:20:42 BOLUS_WIZARD
3 16/03/2019 13:20:41
4 16/03/2019 12:31:18 BOLUS_WIZARD 5.0000
5 16/03/2019 12:27:58 BOLUS_WIZARD
6 16/03/2019 12:27:57
7 16/03/2019 11:00:02 CLOSED_LOOP_MICRO_BOLUS 0.0500
8 16/03/2019 10:55:04 CLOSED_LOOP_MICRO_BOLUS 0.0800
9 16/03/2019 10:50:04 CLOSED_LOOP_MICRO_BOLUS 0.0800
10 16/03/2019 10:45:02 CLOSED_LOOP_MICRO_BOLUS 0.0500
11 16/03/2019 10:40:40 CLOSED_LOOP_FOOD_BOLUS 20.0000
12 16/03/2019 10:27:19 CLOSED_LOOP_FOOD_BOLUS
13 16/03/2019 10:25:04 CLOSED_LOOP_MICRO_BOLUS 0.0800
14 16/03/2019 10:20:04 CLOSED_LOOP_MICRO_BOLUS 0.0800
15 16/03/2019 10:15:02 CLOSED_LOOP_MICRO_BOLUS 0.0500
16 16/03/2019 10:10:04 CLOSED_LOOP_MICRO_BOLUS 0.0800
 hidden batch(es)


with bolus_cte as ( select cast(Format( BolusDate, 'yyyy-MM-dd') as date) dt, format(BolusDate, 'HH') hr, sum(BolusVolDelivered) Bolus --bolusDate, BolusVolDelivered from bolusdata 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
hr SunBolus MonBolus TueBolus WedBolus ThuBolus FriBolus SatBolus
10 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.500000
11 0.000000 0.000000 0.000000 0.000000 0.000000 0.000000 0.050000
 hidden batch(es)


WITH bolus_cte as ( select cast(Format( BolusDate, 'yyyy-MM-dd') as date) dt, format(BolusDate, 'HH') hr, sum(BolusVolDelivered) Bolus from BolusData where BolusSource = 'CLOSED_LOOP_MICRO_BOLUS' group by cast(Format( BolusDate, 'yyyy-MM-dd') as date), format(BolusDate, 'HH') ) select * from bolus_cte;
dt hr Bolus
16/03/2019 00:00:00 10 0.5000
16/03/2019 00:00:00 11 0.0500
 hidden batch(es)