Number of Bookings By Hour V2.0- Rahil

set @startdate1 = '2019-07-01';
set @startdate2 = '2019-09-01';
Select A.return_Date, Count(A.rid) as Total_resv,
Sum(A._1_hr) as 1_hr,Sum(A._2_hr) as 2_hr,Sum(A._3_hr) as 3_hr,
Sum(A._4_hr) as 4_hr,Sum(A._5_hr) as 5_hr,Sum(A._6_hr) as 6_hr,Sum(A._7_hr) as 7_hr, Sum(A._8_hr) as 8_hr,Sum(A._9_hr) as 9_hr,Sum(A._10_hr) as 10_hr,
Sum(A._11_hr) as 11_hr,Sum(A._12_hr) as 12_hr,Sum(A._13_hr) as 13_hr,Sum(A._14_hr) as 14_hr,Sum(A._15_hr) as 15_hr,Sum(A._16_hr) as 16_hr,Sum(A._17_hr) as 17_hr,
Sum(A._18_hr) as 18_hr,Sum(A._19_hr) as 19_hr,Sum(A._20_hr) as 20_hr,Sum(A._21_hr) as 21_hr,Sum(A._22_hr) as 22_hr,Sum(A._23_hr) as 23_hr, Sum(A._24_hr) as 24hr
from
(select distinct r.id as rid,
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 _24_hr,
count(distinct case when hour(r.start_at + interval '8' hour) = 1 then r.id end) as _1_hr,
count(distinct case when hour(r.start_at + interval '8' hour) = 2 then r.id end) as _2_hr,
count(distinct case when hour(r.start_at + interval '8' hour) = 3 then r.id end) as _3_hr,
count(distinct case when hour(r.start_at + interval '8' hour) = 4 then r.id end) as _4_hr,
count(distinct case when hour(r.start_at + interval '8' hour) = 5 then r.id end) as _5_hr,
count(distinct case when hour(r.start_at + interval '8' hour) = 6 then r.id end) as _6_hr,
count(distinct case when hour(r.start_at + interval '8' hour) = 7 then r.id end) as _7_hr,
count(distinct case when hour(r.start_at + interval '8' hour) = 8 then r.id end) as _8_hr,
count(distinct case when hour(r.start_at + interval '8' hour) = 9 then r.id end) as _9_hr,
count(distinct case when hour(r.start_at + interval '8' hour) = 10 then r.id end) as _10_hr,
count(distinct case when hour(r.start_at + interval '8' hour) = 11 then r.id end) as _11_hr,
count(distinct case when hour(r.start_at + interval '8' hour) = 12 then r.id end) as _12_hr,
count(distinct case when hour(r.start_at + interval '8' hour) = 13 then r.id end) as _13_hr,
count(distinct case when hour(r.start_at + interval '8' hour) = 14 then r.id end) as _14_hr,
count(distinct case when hour(r.start_at + interval '8' hour) = 15 then r.id end) as _15_hr,
count(distinct case when hour(r.start_at + interval '8' hour) = 16 then r.id end) as _16_hr,
count(distinct case when hour(r.start_at + interval '8' hour) = 17 then r.id end) as _17_hr,
count(distinct case when hour(r.start_at + interval '8' hour) = 18 then r.id end) as _18_hr,
count(distinct case when hour(r.start_at + interval '8' hour) = 19 then r.id end) as _19_hr,
count(distinct case when hour(r.start_at + interval '8' hour) = 20 then r.id end) as _20_hr,
count(distinct case when hour(r.start_at + interval '8' hour) = 21 then r.id end) as _21_hr,
count(distinct case when hour(r.start_at + interval '8' hour) = 22 then r.id end) as _22_hr,
count(distinct case when hour(r.start_at + interval '8' hour) = 23 then r.id end) as _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')
group by r.id) A
group by A.return_date
Order by A.return_date desc