Loui Duration Query

set @start_at = '2019-03-01 00:00:00';

#Usage by rental hours in SL area
select
concat(rweek, " (",right(wfirst,5),"~",right(wlast,5),")") as rweek,

case
when(region='Nilai') then 'Nilai'
when(region='Cyberjaya') then 'Cyberjaya'
when(region='Seri Kembangan') then 'Seri Kembangan'
when(region='Subang Jaya') then 'Subang Jaya'
when(region='Setia Alam') then 'Setia Alam'
when(region='Shah Alam') then 'Shah Alam'
when(region='Klang') then 'Klang'
when(region='Sepang') then 'Sepang'
when(region='kota kemuning') then 'kota kemuning'
when(region='semenyih') then 'semenyih'
when(region='Puchong') then 'Puchong'
when(region='PJ - South') then 'PJ - South'
when(region='Kajang') then 'Kajang'
when(region='Bangi') then 'Bangi'
when(region='Putrajaya') then 'Putrajaya'
when(region='Seremban') then 'Seremban' end as region,

  00_01_hr,01_02_hr,02_03_hr, 03_04_hr, 04_05_hr, 05_06_hr,06_07_hr,07_08_hr,08_09_hr,09_10_hr,10_11_hr,11_12_hr,
  12_24_hr,
  24_48_hr,
  48__hr,
  #sum(00_01_hr+01_05_hr+05_10_hr+10_12_hr+12_24_hr+24_48_hr+48__hr),
  total
from (
select
  rweek, region, min(date_format(rret,"%Y-%m-%d")) as wfirst, max(date_format(rret,"%Y-%m-%d")) as wlast,
  count(rid) as total,
  count(if(duration < 1, rid, null)) as 00_01_hr,
  count(if(duration >= 1 and duration < 2, rid, null)) as 01_02_hr,
  count(if(duration >= 2 and duration < 3, rid, null)) as 02_03_hr,
  count(if(duration >= 3 and duration < 4, rid, null)) as 03_04_hr,
  count(if(duration >= 4 and duration < 5, rid, null)) as 04_05_hr,
  count(if(duration >= 5 and duration < 6, rid, null)) as 05_06_hr,
  count(if(duration >= 6 and duration < 7, rid, null)) as 06_07_hr,
  count(if(duration >= 7 and duration < 8, rid, null)) as 07_08_hr,
  count(if(duration >= 8 and duration < 9, rid, null)) as 08_09_hr,
  count(if(duration >= 9 and duration < 10, rid, null)) as 09_10_hr,
  count(if(duration >= 10 and duration < 11, rid, null)) as 10_11_hr,
  count(if(duration >= 11 and duration < 12, rid, null)) as 11_12_hr,
  count(if(duration >= 12 and duration < 24, rid, null)) as 12_24_hr,
  count(if(duration >= 24 and duration < 48, rid, null)) as 24_48_hr,
  count(if(duration >= 48, rid, null)) as 48__hr

from (
  select z.region as region, r.id as rid, r.member_id as mid,
    (select dl.birthday
      from driver_licenses dl
      where
        m.id=dl.member_id
      order by dl.id desc limit 1
    ) as dlst_uploaded,
    r.start_at+interval 8 hour as rst, r.end_at+interval 8 hour as rend, r.return_at+interval 8 hour as rret,
    concat(year(r.return_at+interval 8 hour), "-", lpad(week(r.return_at+interval 8 hour, 1),2,"0")) as rweek,
    timestampdiff(minute, r.start_at, r.end_at)/60 as duration
  from reservations r, members m, zones z
  where
    r.state='completed'
    and r.start_zone_id=z.id
    and z.city='sl'
    and r.member_id=m.id
    and m.imaginary in ('normal', 'sofam')
    ######################################################3
    # period set
    and r.return_at+interval 8 hour >= @start_at
    and r.return_at+interval 8 hour < @start_at+interval 3 month
    and r.start_zone_id not in (2,3,101,383,378,712)
) temp
group by region
) temp
group by region
;