Rahil - MFT Calculations

set @start := '2019-04-01 00:00:00';
set @end := '2019-04-31 23:59:59';

Select
B.zoneid,
B.regionname,
Sum(A.TotalMFTs),
(C.AM) as Activemembers
from

(select
week(c.created_at + interval '8' hour) as week,
m.id as mid,
count(distinct case when c.kind = 'subscriptionFee' then c.member_id end) as TotalMFTs
from charges c
join members m
on m.id = c.member_id
where c.created_at + interval '8' hour >= @start
and c.created_at + interval '8' hour <= @end
and m.imaginary = 'normal'
and c.kind = 'subscriptionFee'
#and r.state = 'completed'
Group by week,mid) A

join

(select
distinct m.id as mid,
min(week(r.start_at + interval '8' hour)) as week,
r.id as rid, r.car_id as carid, cc.car_name as carname, z.region as regionname,r.start_zone_id as zoneid
from reservations r
 left join members m on m.id = r.member_id
 left join cars c on c.id = r.car_id
 left join car_classes cc on cc.id = c.car_class_id
 left join zones z on z.id = c.zone_id

where r.start_at + interval '8' hour >= @start
and r.start_at + interval '8' hour <= @end
and r.state = 'completed'
group by mid) B

on A.mid = B.mid

left join

(select
r.start_zone_id as zoneid,
count(distinct case when r.state = 'completed' then r.member_id end) as AM
from reservations r
join members m on m.id = r.member_id
where r.start_at + interval '8' hour >= @start
and r.start_at + interval '8' hour <= @end
and m.imaginary in  ('normal')
and r.state = 'completed'
and r.start_zone_id not in (2,3,101,383,378,697,712,818)
group by zoneid) C

on B.zoneid = C.zoneid

where C.zoneid not in (2,3,101,383,378,697,712,818)

group by B.zoneid