MFT Start time - Each day - Rahil

set @start = "2019-01-01 00:00:00";
select A.date, count(A.mid) as totalmfts,
Sum(B._1_hr) as 1_hr,Sum(B._2_hr) as 2_hr,Sum(B._3_hr) as 3_hr,
Sum(B._4_hr) as 4_hr,Sum(B._5_hr) as 5_hr,Sum(B._6_hr) as 6_hr,Sum(B._7_hr) as 7_hr, Sum(B._8_hr) as 8_hr,Sum(B._9_hr) as 9_hr,Sum(B._10_hr) as 10_hr,
Sum(B._11_hr) as 11_hr,Sum(B._12_hr) as 12_hr,Sum(B._13_hr) as 13_hr,Sum(B._14_hr) as 14_hr,Sum(B._15_hr) as 15_hr,Sum(B._16_hr) as 16_hr,Sum(B._17_hr) as 17_hr,
Sum(B._18_hr) as 18_hr,Sum(B._19_hr) as 19_hr,Sum(B._20_hr) as 20_hr,Sum(B._21_hr) as 21_hr,Sum(B._22_hr) as 22_hr,Sum(B._23_hr) as 23_hr, Sum(B._24_hr) as 24hr

from
(select distinct c.member_id as mid, date(c.created_at + interval '8' hour) as date
from charges c
where c.kind = 'subscriptionFee' and c.created_at + interval '8' hour >= @start) A
left join
(select distinct r.member_id as mid, (YEAR(r.start_at) - YEAR(m.birthday)) as age , z.city as city,hour(r.start_at + interval '8' hour) as hour,
min(r.id) as rid,
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
join members m on r.member_id = m.id
join zones z on r.start_zone_id = z.id
where m.imaginary in ('sofam', 'normal') and r.member_id not in ('125', '127') and r.start_at + interval '8' hour >= @start
group by r.member_id) B
on A.mid = B.mid
where B.city in ('KL','SL','PG','JB')
group by 1
order by 1 desc