Duration Length by Hour - Rahil

set @startdate1 = '2019-07-01';
set @startdate2 = '2019-08-01';
select distinct r.id,
Date(r.return_at + interval '8' hour) as return_Date ,
hour(r.start_at + interval '8' hour) as Resv_start_hour,
count(distinct case when hour(r.start_at + interval '8' hour) = 0 then r.id end) as 00_00_hr,
count(distinct case when hour(r.start_at + interval '8' hour) = 1 then r.id end) as 00_01_hr,
count(distinct case when hour(r.start_at + interval '8' hour) = 2 then r.id end) as 00_02_hr,
count(distinct case when hour(r.start_at + interval '8' hour) = 3 then r.id end) as 00_03_hr,
count(distinct case when hour(r.start_at + interval '8' hour) = 4 then r.id end) as 00_04_hr,
count(distinct case when hour(r.start_at + interval '8' hour) = 5 then r.id end) as 00_05_hr,
count(distinct case when hour(r.start_at + interval '8' hour) = 6 then r.id end) as 00_06_hr,
count(distinct case when hour(r.start_at + interval '8' hour) = 7 then r.id end) as 00_07_hr,
count(distinct case when hour(r.start_at + interval '8' hour) = 8 then r.id end) as 00_08_hr,
count(distinct case when hour(r.start_at + interval '8' hour) = 9 then r.id end) as 00_09_hr,
count(distinct case when hour(r.start_at + interval '8' hour) = 10 then r.id end) as 00_10_hr,
count(distinct case when hour(r.start_at + interval '8' hour) = 11 then r.id end) as 00_11_hr,
count(distinct case when hour(r.start_at + interval '8' hour) = 12 then r.id end) as 00_12_hr,
count(distinct case when hour(r.start_at + interval '8' hour) = 13 then r.id end) as 00_13_hr,
count(distinct case when hour(r.start_at + interval '8' hour) = 14 then r.id end) as 00_14_hr,
count(distinct case when hour(r.start_at + interval '8' hour) = 15 then r.id end) as 00_15_hr,
count(distinct case when hour(r.start_at + interval '8' hour) = 16 then r.id end) as 00_16_hr,
count(distinct case when hour(r.start_at + interval '8' hour) = 17 then r.id end) as 00_17_hr,
count(distinct case when hour(r.start_at + interval '8' hour) = 18 then r.id end) as 00_18_hr,
count(distinct case when hour(r.start_at + interval '8' hour) = 19 then r.id end) as 00_19_hr,
count(distinct case when hour(r.start_at + interval '8' hour) = 20 then r.id end) as 00_20_hr,
count(distinct case when hour(r.start_at + interval '8' hour) = 21 then r.id end) as 00_21_hr,
count(distinct case when hour(r.start_at + interval '8' hour) = 22 then r.id end) as 00_22_hr,
count(distinct case when hour(r.start_at + interval '8' hour) = 23 then r.id end) as 00_23_hr
from reservations r left join members m on r.member_id = m.id
where r.state in ('completed','inUse')
and r.return_at + interval 8 hour >= @startdate1
and r.return_at + interval 8 hour <= @startdate2
and m.imaginary in ('sofam', 'normal')
and r.member_id not in ('125', '127')
and m.state = 'normal'
group by r.id