Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- ############ Query to Find the MFTs whose duration is greater than 3 Hours
- set @startdate1 = '2019-11-01';
- set @startdate2 = '2020-12-01';
- use socar_malaysia;
- Select distinct I.mid as mid, I.name as name, I.email as email, I.Phone as phone, I.return_date as date, Week(I.return_date) as week , I.dur as duration from
- (select A.return_date as return_date, A.rid2 as rid,A.mid as mid, A.provider as provider , A.dur as dur, A.ad_sdur as ad_sdur,A.name as name, A.email as email, A.phone as phone, if(rid2=firstRes,1,0) as mft from
- (select
- distinct r.id as rid2, r.member_id as mid,p.paygate as provider, m.display_name as name, m.email as email, ma.phone as phone,
- ifnull((select min(id) from reservations where state='completed' and r.member_id=member_id group by member_id),0) as firstRes,
- 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 payments p on p.reservation_id = r.id
- left join member_appendixes ma on ma.member_id = m.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')
- and p.paygate is not null
- group by rid2) A) I
- where I.mft = 1
- and I.provider like 'grabpay'
- and I.dur >= 3
- and week(I.return_date) >= '46'
- order by I.return_date asc ;
- ############ Query to Find the MFTs who spent more than 50 with Grabpay
- set @startdate1 = '2019-11-01';
- set @startdate2 = '2020-12-01';
- use socar_malaysia;
- Select distinct I.mid as mid, I.name as name, I.email as email, I.Phone as phone, I.return_date as date, Week(I.return_date) as week , I.dur as duration , I.net_rev from
- (select A.return_date as return_date, A.rid2 as rid,A.mid as mid, A.provider as provider , A.dur as dur, A.name as name, A.email as email, A.phone as phone, if(rid2=firstRes,1,0) as mft ,
- 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,p.paygate as provider, m.display_name as name, m.email as email, ma.phone as phone,
- ifnull((select min(id) from reservations where state='completed' and r.member_id=member_id group by member_id),0) as firstRes,
- round(sum(timestampdiff(minute, r.start_at, r.end_at)/60),2) as dur,
- 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 payments p on p.reservation_id = r.id
- left join member_appendixes ma on ma.member_id = m.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')
- and p.paygate is not null
- 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.mft = 1
- and I.provider like 'grabpay'
- and I.net_rev >= '50'
- and week(I.return_date) >= '46'
- order by I.return_date asc
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement