Количество # вхождений в промежуток времени в SQL

Question

Если у меня есть данные, хранящиеся в БД, как это.

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

Как я могу написать свой запрос, чтобы я мог получить результат, как:

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

Как вы можете сказать, я работаю с шагом 30 минут. Я могу достичь этой серверной стороны, но она требует от меня либо выполнения запроса, либо попадания в базу данных каждый раз с подсчетом строк между двумя значениями времени. Если я смогу собрать эту информацию в 1 запросе, вся моя система будет намного быстрее.

FK - это идентификатор моего ресурса, который зарезервирован.

По сути, я хочу, чтобы пользователь знал, сколько открытых пятен осталось на основе вхождений в БД, не обращаясь к БД несколько раз, если это возможно. Даже QoQ причиняет боль, если мне нужно запускать один на каждые 30 минут приращения дня.

Вы также можете предположить, что для fk 1 и 2 у меня есть известное максимальное время начала и максимальное время окончания, если это имеет значение.

Answer 1

Сначала вы должны каким-то образом создать все временные интервалы, чтобы иметь возможность проверять резервирование. Рекурсивный CTE в основном подходит для чего-то подобного. Таким образом, использование может использовать это 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

сгенерировать следующее табличное выражение:

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

Это все временные интервалы между 10:00 а также 12:00,

Теперь вы можете использовать этот запрос:

;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

чтобы получить желаемый результат:

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