Интересный SQL Присоединиться к датам между датами

Прежде всего, спасибо всем, кто помогает мне решить эту проблему. Я использую SQL 2005, но могу использовать 2008, если в 05 нет решения.

У меня есть строки данных, которые выглядят так:

select * from mySPtable

| myPK | Area | RequestType |  StartDate  |  EndDate  |
   1      SB        ADD        8/14/2011    8/18/2011
   2      NB        RMV        8/16/2011    8/16/2011

Итак, что я хочу сделать, это подсчет суммарного количества запросов для каждой области по дням. Результаты должны быть:

|  myDate  | RequestType |  Area  | myCount |
  8/14/2011      ADD         SB        1
  8/15/2011      ADD         SB        1
  8/16/2011      ADD         SB        1
  8/16/2011      RMV         NB        1
  8/17/2011      ADD         SB        1
  8/18/2011      ADD         SB        1

Как мне это сделать? Я в тупике, и никакое количество googling не помогло.

Ответы

Ответ 1

Вам понадобится либо таблица календаря, либо вы можете сгенерировать ее с помощью CTE. После этого остальная часть запроса должна быть довольно тривиальной. Подход CTE может быть немного сложным из-за проблем с рекурсией и не иметь возможности использовать агрегаты, поэтому ниже я использовал переменную таблицы. Вы также можете сделать это постоянной таблицей, которую вы храните в своей базе данных.

SET NOCOUNT ON

DECLARE @Calendar TABLE (my_date DATETIME NOT NULL)
DECLARE @date DATETIME, @max_date DATETIME

SELECT @date = MIN(StartDate), @max_date = MAX(EndDate) FROM My_Table

WHILE (@date <= @max_date)
BEGIN
    INSERT INTO @Calendar (my_date) VALUES (@date)
    SELECT @date = DATEADD(dy, 1, @date)
END

SELECT
    C.myDate,
    M.RequestType,
    M.Area,
    COUNT(*) AS myCount
FROM
    @Calendar C
INNER JOIN My_Table M ON
    M.StartDate <= C.myDate AND
    M.EndDate >= C.myDate
GROUP BY
    C.myDate,
    M.RequestType,
    M.Area
ORDER BY
    C.myDate,
    M.RequestType,
    M.Area

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

Ответ 2

Похоже, вам может понадобиться "Календарь" . Особенно в рамках более крупной бизнес-организации это станет чрезвычайно полезным.

После создания календаря вы можете получить таблицу со следующим:

SELECT a.isoDate, b.RequestType, b.Area, count(*)
FROM calendar as a
JOIN mySPTable as b
ON a.isoDate between b.StartDate and b.EndDate
WHERE a.isoDate >= [input_start_date] 
      AND a.isoDate < [input_end_date]
GROUP BY a.isoDate, b.RequestType, b.Area

Это приведет к созданию строки для каждой даты в файле календаря, которая находится между датой начала и конца по меньшей мере для одной строки mySPTable.

В качестве побочного примечания также можно создать диапазон дат с рекурсивным CTE, но особенно в долгосрочной перспективе, я бы рекомендовал создавать и использовать файл календаря.
Quick CTE:

WITH DateRange (thisDate) as (SELECT [input_start_date]
                              UNION ALL
                              SELECT DATEADD(dy, 1, thisDate)
                              FROM DateRange
                              WHERE thisDate < [input_end_date])

Ответ 3

Вы можете сделать это с помощью таблицы чисел (начиная с 0). Здесь вместо этого я использую master..spt_values. SQL, вспомогательная таблица чисел

select dateadd(day, N.Number, M.StartDate) as myDate,
       RequestType,
       Area, 
       count(*) as myCount
from mySPtable as M
  inner join master..spt_values as N
    on N.Number <= datediff(day, M.StartDate, M.EndDate)
where N.type = 'P'
group by dateadd(day, N.Number, M.StartDate),
         RequestType,
         Area
order by dateadd(day, N.Number, M.StartDate)