Tracking different types of charges - Insurance as well

######## weekly Revenue Related Info

set @startDate = "2019-11-01 00:00:00";

select
rm.date as date,
rm.resv as resv,
sum(rm.nr) as netrevenues,
sum(rm.rent) as rental,
sum(rm.d2d) as d2d,
sum(rm.insurance) as insurance,
sum(rm.mileage) as mileage,
sum(rm.paid_coupon) as Coupon

from
(select
  Date(r.return_at + interval '8' hour) as date,
  count(r.id) as resv,
  sum(ifnull((select sum(amount) from charges where state='normal' and r.id=reservation_id and kind in ('rent','d2d','oneway','mileage')),0)) as rent,
  sum(ifnull((select sum(amount) from charges where state='normal' and r.id=reservation_id and kind in ('d2d')),0)) as d2d,
  sum(ifnull((select sum(amount) from charges where state='normal' and r.id=reservation_id and kind in ('oneway')),0)) as oneway,
  sum(ifnull((select sum(amount) from charges where state='normal' and r.id=reservation_id and kind in ('mileage')),0)) as mileage,
  sum(ifnull((select sum(amount) from charges where state='normal' and r.id=reservation_id and kind in ('subscriptionFee')),0)) as subscriptionFee,
  sum(ifnull((select sum(amount) from charges where state='normal' and r.id=reservation_id and kind in ('insurance')),0)) as insurance,
  sum(ifnull((select sum(amount) from payments where state='normal' and r.id=reservation_id and paid_type='coupon'),0)) as paid_coupon,
  round(sum(ifnull((select sum(amount) from charges where state='normal' and r.id=reservation_id and kind in ('rent','oneway','d2d','mileage')),0)- ifnull((select sum(amount) from payments where state='normal' and reservation_id=r.id and paid_type ='coupon'),0)),2) as nr
  from reservations r, members m

 where r.member_id=m.id
 and m.imaginary in ('normal','sofam')
 and r.state = 'completed'
 and convert_tz(r.return_at, '+0:00','+8:00') >= @startdate
 group by 1
) rm
group by rm.date
order by 1 desc ;