2 queries - Coupon policy ID and Promo Code ID

############ PROMO CODE ID

set @startdate1 = '2019-07-01';
set @startdate2 = '2019-08-21';
use socar_malaysia;
Select L.week as week , L.pcid as pcid, Sum(L.mft) as MFTs from
(select A.week, A.rid2 as rid, IFNULL(A.pcid,0) as pcid, if(rid2=firstRes,1,0) as mft from
(select
distinct r.id as rid2, r.member_id as mid,IFNULL(co.comment,"") as coupon, co.promo_code_id as pcid,
weekofyear(r.start_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
left join promo_codes pc on co.coupon_policy_id = pc.coupon_policy_id
where r.state in ('completed','inUse')
and (r.start_at + interval '8' hour) >= @startdate1
and m.imaginary in ('sofam', 'normal')
and r.member_id not in ('125', '127')
group by rid2) A) L
Group by 1,2 ;

################# COUPON NAME

set @startdate1 = '2019-07-01';
use socar_malaysia;
Select L.week as week , L.coupon as coupon, L.pcid as pcid, Sum(L.mft) as MFTs from
(select A.week, A.rid2 as rid, IFNULL(A.coupon,0) as coupon, IFNULL(A.pcid,0) as pcid, if(rid2=firstRes,1,0) as mft from
(select
distinct r.id as rid2, r.member_id as mid,IFNULL(co.comment,"") as coupon, co.promo_code_id as pcid,
weekofyear(r.start_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
left join promo_codes pc on co.coupon_policy_id = pc.coupon_policy_id
where r.state in ('completed','inUse')
and (r.start_at + interval '8' hour) >= @startdate1
and m.imaginary in ('sofam', 'normal')
and r.member_id not in ('125', '127')
group by rid2) A) L
Group by 1,2