Разделить временной интервал на несколько

Рейтинг: 0Ответов: 1Опубликовано: 09.01.2023

Подскажите как разделить на дневное/ночное время

Есть ворота со СКУД, въезд/выезд транспорта попадает в базу (MS SQL Server)

Запросом из базы получаем следующую таблицу:

CarID  EnterDateTime  ExitDateTime
885  2023-01-07 02:13:13  2023-01-07 12:59:38
769  2023-01-06 22:57:58  2023-01-07 02:09:18
122  2023-01-07 03:01:38  2023-01-07 14:45:23
575  2023-01-07 19:34:08  2023-01-07 21:48:08
195  2023-01-07 02:13:08  2023-01-07 12:59:28
010  2023-01-07 14:51:28  2023-01-07 20:23:23
906  2023-01-06 22:57:53  2023-01-07 02:09:13
829  2023-01-07 09:01:33  2023-01-07 13:10:08
465  2023-01-07 21:03:33  2023-01-07 21:11:28
312  2023-01-07 20:46:08  2023-01-07 22:47:48
412  2023-01-07 18:34:18  2023-01-07 21:50:13
213  2023-01-07 21:24:43  2023-01-07 21:40:58
985  2023-01-06 20:17:38  2023-01-08 02:09:18

Мне нужно разбить на периоды, например из:

885  2023-01-07 02:13:13  2023-01-07 12:59:38

нужно получить:

1  885  2023-01-07 02:13:13  2023-01-07 10:00:00
2  885  2023-01-07 10:00:00  2023-01-07 12:59:38

или из:

985  2023-01-06 20:17:38  2023-01-08 02:09:18

нужно получить:

1  985  2023-01-06 20:17:38  2023-01-06 21:00:00
2  985  2023-01-06 21:00:00  2023-01-07 10:00:00
3  985  2023-01-07 10:00:00  2023-01-07 21:00:00
4  985  2023-01-07 21:00:00  2023-01-08 02:09:18

Не нашла пока ничего похожего, но может поделитесь ссылкой, или подскажете как правильно написать запрос...

Буду очень признательна за любую Вашу помощь! ;-*

datetime sql-server
Источник: Stack Overflow на русском

Ответы

▲ 3Принят

Генерируем "календарь", включающий все поддиапазоны в диапазоне имеющихся данных. Затем JOIN таблицы данных по пересечению диапазонов и вывод их пересечения.

WITH 
cte1 AS (
  SELECT MIN(CAST(CAST(EnterDateTime AS DATE) AS DATETIME)) MinDateTime,
         DATEADD(DAY, 1, MAX(CAST(CAST(ExitDateTime AS DATE) AS DATETIME))) MaxDateTime
  FROM test         
),
cte2 AS (
  SELECT DATEADD(HOUR, -3, MinDateTime) EnterDateTime,
         DATEADD(HOUR, 10, MinDateTime) ExitDateTime
  FROM cte1
UNION ALL
  SELECT ExitDateTime, DATEADD(DAY, 1, EnterDateTime)
  FROM cte2
  JOIN cte1 ON cte1.MaxDateTime > cte2.EnterDateTime 
)
SELECT test.CarID, 
      CASE WHEN test.EnterDateTime < cte2.EnterDateTime
           THEN cte2.EnterDateTime
           ELSE test.EnterDateTime 
           END EnterDateTime,
      CASE WHEN test.ExitDateTime < cte2.ExitDateTime
           THEN test.ExitDateTime
           ELSE cte2.ExitDateTime
           END ExitDateTime
FROM cte2
JOIN test ON cte2.EnterDateTime < test.ExitDateTime
         AND test.EnterDateTime < cte2.ExitDateTime
ORDER BY test.CarID, cte2.EnterDateTime

step-by-step fiddle