Reservation State by date - Rahil

set @startdate1 = '2019-06-01';
set @startdate2 = '2019-12-01';

Select A.date,
Count(A.rid) as rid,
count(distinct case when A.state in ('completed', 'inUse','reserved') then A.rid end) as Completed_Bookings,
count(distinct case when A.state in ('canceled') then A.rid end) as cancellations,
count(distinct case when A.state in ('fail') then A.rid end) as failed

from

(select
r.id as rid,
r.state as state,
date(r.start_at + interval 8 hour) as date
from reservations r
left join members m on r.member_id = m.id
where r.start_at + interval 8 hour >= @startdate1
and r.start_at + interval 8 hour <= NOW()
and m.imaginary in ('sofam', 'normal')
and r.member_id not in ('125', '127')
group by rid) A
group by 1
order by 1 desc