Socar Points + Insurance - Heng

set @start := '2018-12-31 00:00';

Select A.year,A.month, Round(B.earn,0) as points_earned , Round(A.redeem,0) as points_Redemmed, Round(B.earn - A.redeem,0) as total_Remaining , ROUND(IFNULL(C.Insurance,""),2) as Insurance from

(select
month(p.created_at + interval 8 hour) as month,
Year(p.created_at + interval 8 hour) as year,
sum(p.point) as redeem
from points p
where p.state = 'normal'
and date(p.created_at + interval 8 hour) >= @start
and p.type = 'redeem'
group by 1,2) A
left join
(select
month(p.created_at + interval 8 hour) as month,
Year(p.created_at + interval 8 hour) as year,
sum(p.point) as earn
from points p
where p.state = 'normal'
and date(p.created_at + interval 8 hour) >= @start
and p.type = 'earn'
group by 1,2) B
on A.month = B.month and A.year = B.year
left join
(select month(c.created_at + interval 8 hour) as month,
year(c.created_at + interval 8 hour) as year,
sum(c.amount) as Insurance
from charges c
left join charge_meta  cm on cm.charge_id = c.id
left join reservations r on r.id = c.reservation_id
left join members m on m.id = c.member_id
where c.kind = 'insurance'
and date(c.created_at + interval 8 hour) >= @start
and m.imaginary in ('sofam', 'normal')
and r.member_id not in ('125', '127')
and r.state in ('completed','inUse')
and c.state = 'normal'
group by 1,2) C
on C.month = A.month and A.year = C.year
order by 1,2 asc