Hourly Reservations - Rahil

set @start := '2019-1-6';

select
date(r.start_at + interval '8' hour) as date,
round(sum(timestampdiff(minute, r.start_at, r.end_at)/60),2) as dur,
count(distinct case when r.state = 'completed' then r.id end) as Bookings,

count(distinct case when (r.start_at + interval 8 hour) <= date_add(@start, interval 0 minute)
and (r.end_at + interval 8 hour) >= date_add(@start, interval 5 minute) then r.id end) as 00am,

count(distinct case when (r.start_at + interval 8 hour) <= date_add(@start, interval 60 minute)
and (r.end_at + interval 8 hour) >= date_add(@start, interval 65 minute) then r.id end) as 01am,

count(distinct case when (r.start_at + interval 8 hour) <= date_add(@start, interval 120 minute)
and (r.end_at + interval 8 hour) >= date_add(@start, interval 125 minute) then r.id end) as 02am,

count(distinct case when (r.start_at + interval 8 hour) <= date_add(@start, interval 180 minute)
and (r.end_at + interval 8 hour) >= date_add(@start, interval 185 minute) then r.id end) as 03am,

count(distinct case when (r.start_at + interval 8 hour) <= date_add(@start, interval 240 minute)
and (r.end_at + interval 8 hour) >= date_add(@start, interval 245 minute) then r.id end) as 04am,

count(distinct case when (r.start_at + interval 8 hour) <= date_add(@start, interval 300 minute)
and (r.end_at + interval 8 hour) >= date_add(@start, interval 305 minute) then r.id end) as 05am,

count(distinct case when (r.start_at + interval 8 hour) <= date_add(@start, interval 360 minute)
and (r.end_at + interval 8 hour) >= date_add(@start, interval 365 minute) then r.id end) as 06am,

count(distinct case when (r.start_at + interval 8 hour) <= date_add(@start, interval 420 minute)
and (r.end_at + interval 8 hour) >= date_add(@start, interval 425 minute) then r.id end) as 07am,

count(distinct case when (r.start_at + interval 8 hour) <= date_add(@start, interval 480 minute)
and (r.end_at + interval 8 hour) >= date_add(@start, interval 485 minute) then r.id end) as 08am,

count(distinct case when (r.start_at + interval 8 hour) <= date_add(@start, interval 540 minute)
and (r.end_at + interval 8 hour) >= date_add(@start, interval 545 minute) then r.id end) as 09am,

count(distinct case when (r.start_at + interval 8 hour) <= date_add(@start, interval 600 minute)
and (r.end_at + interval 8 hour) >= date_add(@start, interval 605 minute) then r.id end) as 10am,

count(distinct case when (r.start_at + interval 8 hour) <= date_add(@start, interval 660 minute)
and (r.end_at + interval 8 hour) >= date_add(@start, interval 665 minute) then r.id end) as 11am,

count(distinct case when (r.start_at + interval 8 hour) <= date_add(@start, interval 720 minute)
and (r.end_at + interval 8 hour) >= date_add(@start, interval 725 minute) then r.id end) as 12am,

count(distinct case when (r.start_at + interval 8 hour) <= date_add(@start, interval 780 minute)
and (r.end_at + interval 8 hour) >= date_add(@start, interval 785 minute)then r.id end) as 13am,

count(distinct case when (r.start_at + interval 8 hour) <= date_add(@start, interval 840 minute)
and (r.end_at + interval 8 hour) >= date_add(@start, interval 845 minute) then r.id end) as 14am,

count(distinct case when (r.start_at + interval 8 hour) <= date_add(@start, interval 900 minute)
and (r.end_at + interval 8 hour) >= date_add(@start, interval 905 minute) then r.id end) as 15am,

count(distinct case when (r.start_at + interval 8 hour) <= date_add(@start, interval 960 minute)
and (r.end_at + interval 8 hour) >= date_add(@start, interval 965 minute) then r.id end) as 16am,

count(distinct case when (r.start_at + interval 8 hour) <= date_add(@start, interval 1020 minute)
and (r.end_at + interval 8 hour) >= date_add(@start, interval 1025 minute) then r.id end) as 17am,

count(distinct case when (r.start_at + interval 8 hour) <= date_add(@start, interval 1040 minute)
and (r.end_at + interval 8 hour) >= date_add(@start, interval 1045 minute) then r.id end) as 18am,

count(distinct case when (r.start_at + interval 8 hour) <= date_add(@start, interval 1200 minute)
and (r.end_at + interval 8 hour) >= date_add(@start, interval 1205 minute) then r.id end) as 19am,

count(distinct case when (r.start_at + interval 8 hour) <= date_add(@start, interval 1260 minute)
and (r.end_at + interval 8 hour) >= date_add(@start, interval 1265 minute) then r.id end) as 20am,

count(distinct case when (r.start_at + interval 8 hour) <= date_add(@start, interval 1320 minute)
and (r.end_at + interval 8 hour) >= date_add(@start, interval 1325 minute) then r.id end) as 21am,

count(distinct case when (r.start_at + interval 8 hour) <= date_add(@start, interval 1380 minute)
and (r.end_at + interval 8 hour) >= date_add(@start, interval 1385 minute) then r.id end) as 22am,

count(distinct case when (r.start_at + interval 8 hour) <= date_add(@start, interval 1440 minute)
and (r.end_at + interval 8 hour) >= date_add(@start, interval 1445 minute) then r.id end) as 23am

from reservations r

where (r.start_at + interval 8 hour) >= date_add(@start, interval 0 day)
and (r.start_at + interval 8 hour) <= date_add(@start, interval 1 day)
and r.state = 'completed'

group by date