1

If I have data stored in a db like so.

fk    start    end
1     1000     1100
1     1030     1130
2     1000     1100

How can I write my query so I can get a result like:

fk    timeslot    occurences
1     1000        1
1     1030        2
1     1100        1
2     1000        1
2     1030        1

As you can tell I'm working in 30 min increments. I can achieve this server side but it requires me to either run a Query of Queries or hit the DB every time with counting the rows between the two times. If I can gather this info in 1 query my whole system will be so much faster.

FK is the id of my resource that is being reserved.

Basically I want to let the user know how many open spots are left based on the occurrences in the DB without hitting the DB multiple times if possible. Even QoQ hurts if I have to run one for each 30 min increment of the day.

You can also assume for fk 1 and 2 I have a known max starttime and max endtime if that matters.

James A Mohler
  • 11,060
  • 15
  • 46
  • 72
Leeish
  • 5,203
  • 2
  • 17
  • 45
  • 1
    So what have you tried? – James A Mohler Jan 28 '15 at 16:26
  • I've tried doing it server side and I know I can. I'm asking if it's possible to be done in MSSQL. I don't really know where to start in SQL. I'm thinking it could be done via CASE statements, but there would be like 48 of them which isn't efficient. – Leeish Jan 28 '15 at 16:28
  • Not sure how you got `timeslot` in the result – Pரதீப் Jan 28 '15 at 16:38
  • So the 10:00 - 10:30 timeslot I can just call 10:00 since I know they are 30 min slots. So since there are two reservations in fk 1, one from 10:00 - 11:00 and one from 10:30 - 11:30. From 10:00 - 10:30 there is 1 spot taken and 10:30-11:00 there are two. make sense? – Leeish Jan 28 '15 at 16:41

1 Answers1

2

You have to somehow create all timeslots first, so that you have sth against which reservations can be checked. A recursive CTE is mostly suitable for sth like this. Thus, use can use this CTE:

DECLARE @MinStart TIME = '10:00:00'
DECLARE @MaxEnd TIME   = '12:00:00'

;WITH Timeslots AS (
   SELECT SlotStart = @MinStart, 
          SlotEnd = DATEADD(MINUTE, 30, @MinStart)

   UNION ALL

   SELECT SlotStart = DATEADD(MINUTE, 30, SlotStart), 
          SlotEnd = DATEADD(MINUTE, 30, SlotEnd)
   FROM Timeslots
   WHERE SlotEnd < @MaxEnd
)
SELECT *
FROM Timeslots

to generate the following table expression:

SlotStart           SlotEnd
------------------------------------
10:00:00.0000000    10:30:00.0000000
10:30:00.0000000    11:00:00.0000000
11:00:00.0000000    11:30:00.0000000
11:30:00.0000000    12:00:00.0000000

These are all time-slots between 10:00 and 12:00.

You can now use this query:

;WITH Timeslots AS (
  ... cte statement here ...
)
SELECT fk, COUNT(fk) AS occurrences, t.SlotStart, t.SlotEnd
FROM #Reservations AS r
INNER JOIN Timeslots AS t ON t.SlotStart >= r.start AND t.SlotEnd <= r.[end]
GROUP BY fk, t.SlotStart, t.SlotEnd

to get the result you want:

fk  occurrences SlotStart           SlotEnd
---------------------------------------------
1   1           10:00:00.0000000    10:30:00.0000000
1   2           10:30:00.0000000    11:00:00.0000000
1   1           11:00:00.0000000    11:30:00.0000000
2   1           10:00:00.0000000    10:30:00.0000000
2   1           10:30:00.0000000    11:00:00.0000000
Giorgos Betsos
  • 71,379
  • 9
  • 63
  • 98