Генерируйте набор результатов постепенного увеличения дат в TSQL

Хотя мне очень нравится решение KM, приведенное выше (+1), я должен подвергнуть сомнению ваше предположение об отсутствии цикла - учитывая вероятные диапазоны дат, с которыми будет работать ваше приложение, наличие цикла на самом деле не должно быть настолько дорогим. Основная хитрость заключается в том, чтобы сохранить результаты цикла в промежуточной / кэш-таблице, чтобы чрезвычайно большие наборы запросов не замедляли работу системы из-за повторного вычисления тех же точных дат. Например, каждый запрос вычисляет / кэширует только те диапазоны дат, которые НЕ уже находятся в кеше и которые ему нужны (и предварительно заполняют таблицу некоторым реалистичным диапазоном дат, например ~ 2 года вперед, с диапазоном, определяемым бизнес-потребностями вашего приложения).

Что я бы порекомендовал: создать вспомогательную таблицу чисел и использовать ее для генерации списка дат. Вы также можете использовать рекурсивный CTE, но он может не работать так же хорошо, как присоединение к вспомогательной таблице чисел. См. SQL, Вспомогательная таблица чисел для информации по обоим параметрам.

создать временную таблицу с целыми числами от 0 до разницы между двумя датами.

SELECT DATE_ADD(@Start, INTERVAL tmp_int DAY) AS the_date FROM int_table;

Другой вариант - создать соответствующую функцию в .NET. Вот как это выглядит:

[Microsoft.SqlServer.Server.SqlFunction(
  DataAccess = DataAccessKind.None,
  FillRowMethodName = "fnUtlGetDateRangeInTable_FillRow",
  IsDeterministic = true,
  IsPrecise = true,
  SystemDataAccess = SystemDataAccessKind.None,
  TableDefinition = "d datetime")]
public static IEnumerable fnUtlGetDateRangeInTable(SqlDateTime startDate, SqlDateTime endDate)
{
    // Check if arguments are valid

    int numdays = Math.Min(endDate.Value.Subtract(startDate.Value).Days,366);
    List<DateTime> res = new List<DateTime>();
    for (int i = 0; i <= numdays; i++)
        res.Add(dtStart.Value.AddDays(i));

    return res;
}

public static void fnUtlGetDateRangeInTable_FillRow(Object row, out SqlDateTime d)
{
    d = (DateTime)row;
}

По сути, это прототип, и его можно сделать намного умнее, но он иллюстрирует идею. По моему опыту, в течение небольшого или среднего промежутка времени (например, пару лет) эта функция работает лучше, чем реализованная в T-SQL. Еще одна приятная особенность версии CLR заключается в том, что она не создает временную таблицу.

Ответ @KM сначала создает таблицу чисел и использует ее для выбора диапазона дат. Чтобы сделать то же самое без таблицы временных номеров:

DECLARE  @Start datetime
         ,@End  datetime
DECLARE @AllDates table
        (Date datetime)

SELECT @Start = 'Mar 1 2009', @End = 'Aug 1 2009';

WITH Nbrs_3( n ) AS ( SELECT 1 UNION SELECT 0 ),
     Nbrs_2( n ) AS ( SELECT 1 FROM Nbrs_3 n1 CROSS JOIN Nbrs_3 n2 ),
     Nbrs_1( n ) AS ( SELECT 1 FROM Nbrs_2 n1 CROSS JOIN Nbrs_2 n2 ),
     Nbrs_0( n ) AS ( SELECT 1 FROM Nbrs_1 n1 CROSS JOIN Nbrs_1 n2 ),
     Nbrs  ( n ) AS ( SELECT 1 FROM Nbrs_0 n1 CROSS JOIN Nbrs_0 n2 )

    SELECT @Start+n-1 as Date
        FROM ( SELECT ROW_NUMBER() OVER (ORDER BY n)
            FROM Nbrs ) D ( n )
    WHERE n <= DATEDIFF(day,@Start,@End)+1 ;

Конечно, если вы делаете это часто, постоянная таблица может быть более производительной.

Вышеупомянутый запрос является измененной версией из этой статьи , в котором обсуждается создание последовательностей и дается много возможных методов. Мне понравился этот, поскольку он не создает временную таблицу и не ограничивается количеством элементов в таблице sys.objects .

Вышеупомянутый запрос является модифицированной версией из этой статьи , в которой обсуждается создание последовательностей и дается множество возможных методов. Мне понравился этот, поскольку он не создает временную таблицу и не ограничивается количеством элементов в таблице sys.objects .

Вышеупомянутый запрос является модифицированной версией из этой статьи , в которой обсуждается создание последовательностей и дается множество возможных методов. Мне понравился этот, так как он не создает временную таблицу и не ограничен количеством элементов в таблице sys.objects .

В следующем примере используется рекурсивный CTE (SQL Server 2005 +):

WITH dates AS (
     SELECT CAST('2009-01-01' AS DATETIME) 'date'
     UNION ALL
     SELECT DATEADD(dd, 1, t.date) 
       FROM dates t
      WHERE DATEADD(dd, 1, t.date) <= '2009-02-01')
SELECT ...
  FROM TABLE t
  JOIN dates d ON d.date = t.date --etc.

Для того, чтобы этот метод работал, вам необходимо выполнить одну настройку таблицы времени:

SELECT TOP 10000 IDENTITY(int,1,1) AS Number
    INTO Numbers
    FROM sys.objects s1
    CROSS JOIN sys.objects s2
ALTER TABLE Numbers ADD CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number)

После того, как таблица номеров настроена, используйте этот запрос:

SELECT
    @Start+Number-1
    FROM Numbers
    WHERE Number<=DATEDIFF(day,@Start,@End)+1

для их захвата выполните:

DECLARE  @Start datetime
         ,@End  datetime
DECLARE @AllDates table
        (Date datetime)

SELECT @Start = 'Mar 1 2009', @End = 'Aug 1 2009'

INSERT INTO @AllDates
        (Date)
    SELECT
        @Start+Number-1
        FROM Numbers
        WHERE Number<=DATEDIFF(day,@Start,@End)+1

SELECT * FROM @AllDates

вывод:

Date
-----------------------
2009-03-01 00:00:00.000
2009-03-02 00:00:00.000
2009-03-03 00:00:00.000
2009-03-04 00:00:00.000
2009-03-05 00:00:00.000
2009-03-06 00:00:00.000
2009-03-07 00:00:00.000
2009-03-08 00:00:00.000
2009-03-09 00:00:00.000
2009-03-10 00:00:00.000
....
2009-07-25 00:00:00.000
2009-07-26 00:00:00.000
2009-07-27 00:00:00.000
2009-07-28 00:00:00.000
2009-07-29 00:00:00.000
2009-07-30 00:00:00.000
2009-07-31 00:00:00.000
2009-08-01 00:00:00.000

(154 row(s) affected)