City level dur > 24 hours - Rahil

set @startdate1 = date_sub(CURDATE(), interval 15 month);
set @startdate2 = date_add(CURDATE(), interval 10 day);
use socar_malaysia;
select month(A.return_date) as month, year(A.return_date) as year,
count(distinct case when A.city like 'KL' then A.rid end) as KL_res,
count(distinct case when A.city like 'SL' then A.rid end) as SL_res,
count(distinct case when A.city like 'PG' then A.rid end) as PG_res,
count(distinct case when A.city like 'JB' then A.rid end) as JB_res,
count(distinct case when A.dur >= 24 and A.city like 'KL' then A.rid end) as KL_24_plus,
count(distinct case when A.dur >= 24 and A.city like 'SL' then A.rid end) as SL_24_plus,
count(distinct case when A.dur >= 24 and A.city like 'PG' then A.rid end) as PG_24_plus,
count(distinct case when A.dur >= 24 and A.city like 'JB' then A.rid end) as JB_24_plus,
ROUND((count(distinct case when A.dur >= 24 and A.city like 'KL' then A.rid end)/count(distinct case when A.city like 'KL' then A.rid end))*100,2) as 24_plus_pct_out_of_total_KL,
ROUND((count(distinct case when A.dur >= 24 and A.city like 'SL' then A.rid end)/count(distinct case when A.city like 'SL' then A.rid end))*100,2) as 24_plus_pct_out_of_total_SL,
ROUND((count(distinct case when A.dur >= 24 and A.city like 'PG' then A.rid end)/count(distinct case when A.city like 'PG' then A.rid end))*100,2) as 24_plus_pct_out_of_total_PG,
ROUND((count(distinct case when A.dur >= 24 and A.city like 'JB' then A.rid end)/count(distinct case when A.city like 'JB' then A.rid end))*100,2) as 24_plus_pct_out_of_total_JB
from
(select
distinct r.id as rid, r.member_id as mid, z.city as city,
round(sum(timestampdiff(minute, r.start_at, r.end_at)/60),2) as dur,
round(sum(floor(timestampdiff(minute, r.start_at, r.end_at)/60/24)*12+if(mod(timestampdiff(hour, r.start_at, r.end_at),24)>=12,12,mod(timestampdiff(minute, r.start_at, r.end_at)/60,24))),2) as ad_sdur,
Date(r.return_at + interval '8' hour) as return_Date
from reservations r left join members m on r.member_id = m.id
left join zones z on z.id = r.start_zone_id
where r.state in ('completed')
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 rid) A
group by 1,2
order by 2 asc , 1 asc