Day Pass - Paid Vs Promo

	select
	p.reservation_id as rid,
	c.comment as couponname,
    date(r.occupy_start_at + interval '8' hour) as date,
	hour(r.occupy_start_at + interval '8' hour) as startingtime,
    timestampdiff(minute,CONVERT_TZ(r.start_at, '+00:00', '+8:00'), CONVERT_TZ(r.end_at, '+00:00', '+8:00'))/60 as ridelength,
    date(c.activated_at + interval '8' hour) as activateddate,
    date(c.used_at + interval '8' hour) as useddate,
	sum(case when p.state = 'normal' and p.paid_type = 'card' then p.amount end) as NetRev,
	sum(case when p.state = 'normal' and p.paid_type = 'coupon' then p.amount end) as coupRev

	from payments p

	left join reservations r
	on p.reservation_id = r.id

	left join coupons c
	on c.reservation_id = p.reservation_id

	where p.state = 'normal'
	and r.start_at + interval '8' hour >= '2019-3-1'
	and r.start_at + interval '8' hour < '2019-4-1'
	and r.state = 'completed'

	group by  rid

	order by p.amount desc