MFT , Transaction Date vs Completed Date - Rahil for Amri

set @start := '2019-1-1';

select P.mid, P.created_date as MFT_date, IFNULL(Q.completed_date, "0") as completed_date from

(select distinct c.member_id as mid,
date(c.created_at + interval 8 hour) as created_date
from charges c join members m on m.id = c.member_id
where c.created_at + interval '8' hour >= @start
and m.imaginary in ('sofam', 'normal')
and c.kind = 'subscriptionFee') P
left join
(Select A.mid as mid, A.first_rid as first_rid, B.date as completed_date from
(select distinct r.member_id as mid,
min(r.id) as first_rid
from reservations r join members m on m.id = r.member_id
where m.imaginary in ('sofam', 'normal')
and r.state = 'completed'
group by 1) A
left join
(select distinct r.id as rid,
date(r.created_at + interval '8' hour) as date
from reservations r ) B
on B.rid = A.first_rid ) Q
on Q.mid = P.mid