One Row, Multiple GROUPs

Question

I have a list of reservations in a MySQL db, and I want to determine total number of slots reserved in 15 minute intervals. To do that, I need to 'unroll' (probably not the right word) each reservation, determine the overlap, and show total usage.

The table is a list of reservations:

ResID              StartTime                EndTime         SlotsUsed  
1            2010-06-13 12:00:00      2010-06-13 12:59:00      10  
2            2010-06-13 12:00:00      2010-06-13 12:29:00      5  
3            2010-06-13 12:30:00      2010-06-13 13:29:00      15  

And the output I want is something like this:

       Time            SlotsInUse  
2010-06-13 12:00:00        15   
2010-06-13 12:15:00        15  
2010-06-13 12:30:00        25  
2010-06-13 12:45:00        25  
2010-06-13 13:00:00        15  

Outside of SQL, I can do this pretty easily with date-time math & a list/array. But is there anyway to do it in the SELECT statement itself, somehow GROUPing items just right, and using sum()?

Answer 1

What you could do is query the total range beforehand so you have begind and end times. Then you can generate one huge SQL using UNION that will give you results for all those intervals, like so:

SELECT Sum(SlotsUsed), $interval1 FROM table WHERE startime <= $interval1 AND endtime >= $interval1
UNION
SELECT Sum(SlotsUsed), $interval2 FROM table WHERE startime <= $interval2 AND endtime >= $interval2
UNION
...

No clue however, if this is feasible.

In DB2 you could do this to improve the size of the Query, maybe there is something similar in MySQL:

SELECT Sum(SlotsUsed), I.Interval
FROM table, (VALUES ($interval1), ($interval2), ..., ($intervalN) ) I(Interval)
WHERE table.startime <= I.Interval AND table.endtime >= I.Interval
GROUP BY I.Interval

Answer 2

This may work for you. I didn't test it and I usually have to fix my sql when I translate to actual sql...

• Create temp table (timetable) with datetime column
• Insert 15 minute intervals you want to report on
• select time,sum(slotsused) from temp left outer join reservations on timetable between(starttime,endtime) group by time

Answer 3

I recommend using a loop. You can find out more about loops at Database Journal.

I'm not sure how but if you can combine the results from each time through the loop change the insert statement to a select. But since I don't know how to do that or if it is possible you can create a temporary table easier and then just select * from it. This is untested pseudocode but you can do something like:

Create tempTable Values ResID, StartTime, EndTime, SlotsUsed 
Declare @min datetime
Declare @max datetime
Set @min = (SELECT MIN(StartTime) FROM Table)
Set @max = (SELECT MAX(EndTime) FROM Table)

Some how round @min to nearest hour or 15 minute increment
for example : 2010-06-13 12:10:00 to 2010-06-13 12:00:00
              2010-06-13 12:34:00 to 2010-06-13 12:00:00 OR 2010-06-13 12:30:00

DECLARE @case_exit int
SET @counter = 0
while @counter < 1
  begin
  insert into tempTable Time = @min, SlotsInUse = (
        SELECT SUM(SlotsUsed) FROM table WHERE @min => StartTime AND @min =< EndTime)
  @min = @min + 15 minutes
  end

SELECT * from TempTable

Answer 4

The trick to this is to get a list of intevals. To do that, you want a Numbers or Tally table which is a static table that contains a sequential list of integers.

Create Temporary Table Test
    (
    ResId int not null
    , StartTime datetime not null
    , EndTime datetime not null
    , SlotsUsed int not null
    )
Insert Test(ResId, StartTime, EndTime, SlotsUsed) Values(1, '2010-06-13 12:00:00','2010-06-13 12:59:00',10)  
Insert Test(ResId, StartTime, EndTime, SlotsUsed) Values(2, '2010-06-13 12:00:00','2010-06-13 12:29:00',5 )
Insert Test(ResId, StartTime, EndTime, SlotsUsed) Values(3, '2010-06-13 12:30:00','2010-06-13 13:29:00',15   )
------
Create Temporary Table Numbers ( Value int not null )
Insert Numbers(Value) Values(1)
Insert Numbers(Value) Values(2)
Insert Numbers(Value) Values(3)
Insert Numbers(Value) Values(4)
Insert Numbers(Value) Values(5)
Insert Numbers(Value) Values(6)
...

Select Intervals.[Date], Sum(SlotsUsed)
From    (
        Select Date_Add( '2010-06-13 12:00:00', INTERVAL + (15 * N.Value) MINUTE ) As [Date], T.SlotsUsed
        From Test As T
            Join Numbers As N
                On Date_Add( '2010-06-13 12:00:00', INTERVAL + (15 * N.Value) MINUTE ) Between T.StartTime And T.EndTime
        ) As Intervals
Group By Intervals.[Date]

Here I arbitrarily chose 2010-06-13 12:00:00 as my start time to determine intervals. However, you could just as easily use DATE(T.StartTime) as long as your Numbers table is big enough to account for all intervals in a given day (i.e. has at least 96 values).