Coupon Study W.O.W - For Tracker - Rahil

set @startdate1 = date_sub(CURDATE(), interval 1 month);
set @startdate2 = date_add(CURDATE(), interval 10 day);
use socar_malaysia;
use socar_malaysia;
Select I.week as week,I.coupon as coupon, I.cpid as cpid, 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(I.ad_sdur) as ad_sdur, Sum(nuse_rnd) as nuse_rnd, Sum(res_d2d) as res_d2d,sum(res_oneway) as res_oneway,
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.week as week, 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.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,
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,
weekofyear(r.return_at + interval '8' hour) as week,
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
where r.state in ('completed','inUse','reserved')
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')
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.week > Weekofyear(now()) - 3
Group by I.week, I.cpid
order by I.week, gross_rev desc;