F11 - Coupon Study - For Tracker

# Double Click on month to get everything in descending order of months (After running the query)

set @startdate1 = date_sub(CURDATE(), interval 5 month);
set @startdate2 = date_sub(CURDATE(), interval 1 day);
use socar_malaysia;
Select I.month as month,I.coupon as coupon, count(distinct case when I.mid is not null then I.mid end) as Mau,count(distinct case when I.rid is not null then I.rid end) as total_resv,
Sum(I.dur) as dur, sum(gross_rev) as gross_rev,sum(Coupon_Spent) as Coupon_Spent,sum(net_rev) as net_rev,sum(mft) as mft from
(select A.month as month, A.mid as mid, A.rid2 as rid, A.coupon as coupon,A.cpid as cpid, A.dur as dur, A.ad_sdur as ad_sdur, A.distance as distance,
A.nuse_rnd as nuse_rnd,A.res_d2d as res_d2d,A.res_oneway as res_oneway ,IFNULL(E.charges,0) as gross_rev, IFNULL(F.coupon_Spent,0) as Coupon_Spent,
round((IFNULL(E.charges,0) - IFNULL(F.coupon_Spent,0)),2) as net_rev,if(rid2=firstRes,1,0) as mft from
(select
distinct r.id as rid2, r.member_id as mid,IFNULL(co.coupon_policy_id,"none") as cpid,IFNULL(co.comment,"none") as coupon,
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,
IFNULL(sum(ra.mileage),0) as distance,
count(if(r.way='round', r.id, null)) as nuse_rnd,
count(if(r.way='d2d', r.id, null)) as res_d2d,
count(if(r.way in('oneway','onewayReturn' ), r.id, null)) as res_oneway,
Date(r.return_at + interval '8' hour) as return_Date,
Month(r.return_at + interval '8' hour) as Month,
ifnull((select min(id) from reservations where state='completed' and r.member_id=member_id group by member_id),0) as firstRes
from reservations r left join members m on r.member_id = m.id
left join coupons co on co.reservation_id = r.id
left join reservation_appendixes ra on ra.reservation_id = r.id
where r.state in ('completed','inUse','reserved','returnDelayed','fail','canceled')
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')
and (YEAR(r.start_at) - YEAR(m.birthday)) < 100
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) I
where I.coupon not in ('none')
and I.month > month(now()) - 5
Group by I.month, I.cpid
order by month, gross_rev desc;