MFTs by Hour - Rahil

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

select
A.date,
count(distinct mid) as TotMFTs,
count(distinct case when dur <= 1 then mid end) AS "<1",
count(distinct case when dur <= 2 then mid end) AS "<2",
count(distinct case when dur <= 3 then mid end) AS "<3",
count(distinct case when dur <= 4 then mid end) AS "<4",
count(distinct case when dur <= 5 then mid end) AS "<5",
count(distinct case when dur > 6 and dur <= 12 then mid end) AS "6-12",
count(distinct case when dur > 12 and dur <= 24 then mid end) AS "12-24",
count(distinct case when dur > 24 and dur <= 48 then mid end) AS "24-48",
count(distinct case when dur > 48 then mid end) AS ">48"
from(
select
    r.id as rid,
    Date(r.return_at + interval 8 hour) as date,
    timestampdiff(minute, r.start_at, r.end_at)/60 as dur,
    ifnull((select min(id) from reservations where state in ('completed') and r.member_id=member_id group by member_id),0) as firstRes,
    m.id as mid,
    str_to_date(m.birthday, '%Y%m%d') as birthdate,
    r.way as way
from cars c, reservations r, members m, zones z
where c.id = r.car_id
    and m.id = r.member_id
    and z.id = r.start_zone_id
    and z.id not in (2,3,101)
    and m.imaginary in ('normal', 'sofam')
    and r.state in ('completed')
    and convert_tz(r.created_at, '+00:00', '+8:00') >= @startDate
having rid=firstRes) A
group by 1
order by 1 desc