NSPH Check - Duration level - 0-1, 1-2, 2-3

set @startdate1 = date_sub(CURDATE(), interval 6 month);
set @startdate2 = date_add(CURDATE(), interval 10 day);
use socar_malaysia;

select
  L.return_Date,
  count(L.rid) as total,
  sum(L.net_rev) as net_rev,
  count(if(L.duration < 1, L.rid, null)) as 00_01_hr,
  count(if(L.duration >= 1 and L.duration < 2, L.rid, null)) as 01_02_hr,
  count(if(L.duration >= 2 and L.duration < 3, L.rid, null)) as 02_03_hr,
  count(if(L.duration >= 3 and L.duration < 4, L.rid, null)) as 03_04_hr,
  count(if(L.duration >= 4 and L.duration < 5, L.rid, null)) as 04_05_hr,
  count(if(L.duration >= 5 and L.duration < 12, L.rid, null)) as 05_12_hr,
  count(if(L.duration >= 12 and L.duration < 24, L.rid, null)) as 12_24_hr,
  count(if(L.duration >= 24 and L.duration < 48, L.rid, null)) as 24_48_hr,
  count(if(L.duration >= 48, L.rid, null)) as 48__hr ,
 ##### Net_rev
  Sum(if(L.duration < 1, L.net_rev, null)) as 00_01_hr_amount,
  Sum(if(L.duration >= 1 and L.duration < 2, L.net_rev, null)) as 01_02_hr_amount,
  Sum(if(L.duration >= 2 and L.duration < 3, L.net_rev, null)) as 02_03_hr_amount,
  Sum(if(L.duration >= 3 and L.duration < 4, L.net_rev, null)) as 03_04_hr_amount,
  Sum(if(L.duration >= 4 and L.duration < 5, L.net_rev, null)) as 04_05_hr_amount,
  Sum(if(L.duration >= 5 and L.duration < 12, L.net_rev, null)) as 05_12_hr_amount,
  Sum(if(L.duration >= 12 and L.duration < 24, L.net_rev, null)) as 12_24_hr_amount,
  Sum(if(L.duration >= 24 and L.duration < 48, L.net_rev, null)) as 24_48_hr_amount,
  Sum(if(L.duration >= 48, L.net_rev, null)) as 48__hr_amount,
   ##### Coupon_check
  Sum(if(L.duration < 1, L.crid, null)) as 00_01_hr_Coupon,
  Sum(if(L.duration >= 1 and L.duration < 2, L.crid, null)) as 01_02_hr_Coupon,
  Sum(if(L.duration >= 2 and L.duration < 3, L.crid, null)) as 02_03_hr_Coupon,
  Sum(if(L.duration >= 3 and L.duration < 4, L.crid, null)) as 03_04_hr_Coupon,
  Sum(if(L.duration >= 4 and L.duration < 5, L.crid, null)) as 04_05_hr_Coupon,
  Sum(if(L.duration >= 5 and L.duration < 12, L.crid, null)) as 05_12_hr_Coupon,
  Sum(if(L.duration >= 12 and L.duration < 24, L.crid, null)) as 12_24_hr_Coupon,
  Sum(if(L.duration >= 24 and L.duration < 48, L.crid, null)) as 24_48_hr_Coupon,
  Sum(if(L.duration >= 48, L.crid, null)) as 48__hr_Coupon,
  ##### Duration check
  Sum(if(L.duration < 1, L.duration, null)) as 00_01_hr_amount,
  Sum(if(L.duration >= 1 and L.duration < 2, L.duration, null)) as 01_02_hr_duration,
  Sum(if(L.duration >= 2 and L.duration < 3, L.duration, null)) as 02_03_hr_duration,
  Sum(if(L.duration >= 3 and L.duration < 4, L.duration, null)) as 03_04_hr_duration,
  Sum(if(L.duration >= 4 and L.duration < 5, L.duration, null)) as 04_05_hr_duration,
  Sum(if(L.duration >= 5 and L.duration < 12, L.duration, null)) as 05_12_hr_duration,
  Sum(if(L.duration >= 12 and L.duration < 24, L.duration, null)) as 12_24_hr_duration,
  Sum(if(L.duration >= 24 and L.duration < 48, L.duration, null)) as 24_48_hr_duration,
  Sum(if(L.duration >= 48, L.duration, null)) as 48__hr_duration

 from

(select A.return_date as return_date, A.rid2 as rid, A.dur as duration, A.ad_sdur as ad_sdur, IFNULL(E.charges,0) as gross_rev, A.crid as crid,
IFNULL(F.coupon_Spent,0) as Coupon_Spent,
round((IFNULL(E.charges,0) - IFNULL(F.coupon_Spent,0)),2) as net_rev from
(select
distinct r.id as rid2, r.member_id as mid, count(distinct case when co.comment is not null then co.reservation_id end) as crid,
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 reservation_appendixes ra on ra.reservation_id = r.id
left join coupons co on co.reservation_id = r.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 rid2) A
left join
(select c.reservation_id as rid, sum(c.amount) as charges from charges c
where c.state='normal' and c.kind in ('rent','oneway','d2d','mileage','insurance')
group by rid) E on A.rid2 = E.rid
left join
(select p.reservation_id as rid, month(p.created_at + interval '8' hour) as month,Year(p.created_at + interval '8' hour) as year, IFNULL(p.amount,0) as coupon_Spent from payments p
where p.state = 'normal' and p.paid_type = 'coupon'
group by p.reservation_id) F on A.rid2 = F.rid) L
group by 1
order by 1 desc