Number of reservations on same day by each member - Rahil

################ Daily


set @startdate1 = '2019-07-01';
set @startdate2 = '2020-02-01';
use socar_malaysia;
select
I.date,
  count(I.mid) as total_AMs,
  sum(I.resv) as reservations,
  count(if(I.resv = 1 , I.mid, null)) as 01_resv,
  count(if(I.resv = 2 , I.mid, null)) as 02_resv,
  count(if(I.resv = 3 , I.mid, null)) as 03_resv,
  count(if(I.resv = 4 , I.mid, null)) as 04_resv,
  count(if(I.resv = 5 , I.mid, null)) as 05_resv,
  count(if(I.resv = 6 , I.mid, null)) as 06_resv,
  count(if(I.resv = 7 , I.mid, null)) as 07_resv,
  count(if(I.resv = 8 , I.mid, null)) as 08_resv,
  count(if(I.resv = 9 , I.mid, null)) as 09_resv,
  count(if(I.resv = 10 , I.mid, null)) as 10_resv,
  count(if(I.resv = 11 , I.mid, null)) as 11_resv,
  count(if(I.resv >= 12 , I.mid, null)) as 12_resv,
  count(if(I.resv >= 24 , I.mid, null)) as 24_resv,
  count(if(I.resv >= 48, I.mid, null)) as 48_resv
  from
(select date(A.return_date) as date, A.mid as mid, count(A.rid2) as resv from
(select
distinct r.id as rid2, r.member_id as mid,YEAR(r.start_at) - YEAR(m.birthday) as age,
round(sum(timestampdiff(minute, r.start_at, r.end_at)/60),2) as dur,
Date(r.return_at + interval '8' hour) as return_Date
from reservations r left join members m on r.member_id = m.id
left join reservation_appendixes ra on ra.reservation_id = r.id
where r.state in ('completed')
and r.return_at + interval 8 hour >= @startdate1
and r.return_at + interval 8 hour <= @startdate2
and m.imaginary in ('sofam', 'normal')
and r.member_id not in ('125', '127')
and ra.performer not in ('admin')
group by rid2) A
group by 1,2) I
group by 1
order by 1 desc;

############# Weekly

set @startdate1 = '2019-07-01';
set @startdate2 = '2020-02-01';
use socar_malaysia;
select
I.week,
  count(I.mid) as total_AMs,
  sum(I.resv) as reservations,
  count(if(I.resv = 1 , I.mid, null)) as 01_resv,
  count(if(I.resv = 2 , I.mid, null)) as 02_resv,
  count(if(I.resv = 3 , I.mid, null)) as 03_resv,
  count(if(I.resv = 4 , I.mid, null)) as 04_resv,
  count(if(I.resv = 5 , I.mid, null)) as 05_resv,
  count(if(I.resv = 6 , I.mid, null)) as 06_resv,
  count(if(I.resv = 7 , I.mid, null)) as 07_resv,
  count(if(I.resv = 8 , I.mid, null)) as 08_resv,
  count(if(I.resv = 9 , I.mid, null)) as 09_resv,
  count(if(I.resv = 10 , I.mid, null)) as 10_resv,
  count(if(I.resv = 11 , I.mid, null)) as 11_resv,
  count(if(I.resv >= 12 , I.mid, null)) as 12_resv,
  count(if(I.resv >= 24 , I.mid, null)) as 24_resv,
  count(if(I.resv >= 48, I.mid, null)) as 48_resv
  from
(select week(A.return_date) as week, A.mid as mid, count(A.rid2) as resv from
(select
distinct r.id as rid2, r.member_id as mid,
round(sum(timestampdiff(minute, r.start_at, r.end_at)/60),2) as dur,
Date(r.return_at + interval '8' hour) as return_Date
from reservations r left join members m on r.member_id = m.id
left join reservation_appendixes ra on ra.reservation_id = r.id
where r.state in ('completed')
and r.return_at + interval 8 hour >= @startdate1
and r.return_at + interval 8 hour <= @startdate2
and m.imaginary in ('sofam', 'normal')
and r.member_id not in ('125', '127')
and ra.performer not in ('admin')
group by rid2) A
group by 1,2) I
group by 1
order by 1 desc;