Сгенерируйте набор результатов по возрастанию дат в TSQL

Рассмотрим необходимость создания набора дат. У нас есть даты начала и окончания, и мы хотели бы создать список дат между ними.

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

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

--need to fill @AllDates. Trying to avoid looping. 
-- Surely if a better solution exists.

Рассмотрим текущую реализацию с помощью цикла WHILE:

DECLARE @dCounter datetime
SELECT @dCounter = @Start
WHILE @dCounter <= @End
BEGIN
 INSERT INTO @AllDates VALUES (@dCounter)
 SELECT @[email protected]+1 
END

Вопрос:. Как бы вы создали набор дат, которые находятся в пределах пользовательского диапазона, используя T-SQL? Предположим, что SQL 2005+. Если ваш ответ использует функции SQL 2008, отметьте как таковой.

Ответ 1

Если ваши даты не более чем на 2047 дней друг от друга:

declare @dt datetime, @dtEnd datetime
set @dt = getdate()
set @dtEnd = dateadd(day, 100, @dt)

select dateadd(day, number, @dt)
from 
    (select distinct number from master.dbo.spt_values
     where name is null
    ) n
where dateadd(day, number, @dt) < @dtEnd

Ответ 2

В следующем случае используется рекурсивный 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.

Ответ 3

Чтобы этот метод работал, вам нужно выполнить эту настройку в один раз:

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)

Как только таблица Numbers настроена, используйте этот запрос:

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)

Ответ 4

@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.

Ответ 5

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

select top 100000 dateadd(d,incr,'2010-04-01') as dt from
(select  incr = row_number() over (order by object_id, column_id), * from
(
select a.object_id, a.column_id from  sys.all_columns a cross join sys.all_columns b
) as a
) as b

Обратите внимание, что вложение предназначено для упрощения управления и преобразования в представления и т.д.

Ответ 6

Другой вариант - создать соответствующую функцию в .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 заключается в том, что она не создает временную таблицу.

Ответ 7

Обзор

Здесь моя версия (совместимая с 2005 годом). Преимущества этого подхода заключаются в следующем:

вы получаете функцию общего назначения, которую вы можете использовать для ряда подобных сценариев; не ограничиваясь только датами
диапазон не ограничен содержимым существующей таблицы
вы можете легко изменить прирост (например, получать дату каждые 7 дней, а не каждый день).
вам не нужен доступ к другим каталогам (т.е. мастер)
движок sql, способный сделать некоторую оптимизацию TVF, которая не может с выражением while
generate_series используется в некоторых других dbs, поэтому это может помочь сделать ваш код инстинктивно знакомым с более широкой аудиторией.

SQL Fiddle: http://sqlfiddle.com/#!6/c3896/1

код

Функция многократного использования для создания диапазона чисел на основе заданных параметров:

create function dbo.generate_series
(
      @start bigint
    , @stop bigint
    , @step bigint = 1
    , @maxResults bigint = 0 --0=unlimitted
)
returns @results table(n bigint)
as
begin

    --avoid infinite loop (i.e. where we're stepping away from stop instead of towards it)
    if @step = 0 return
    if @start > @stop and @step > 0 return
    if @start < @stop and @step < 0 return

    --ensure we don't overshoot
    set @stop = @stop - @step

    --treat negatives as unlimited
    set @maxResults = case when @maxResults < 0 then 0 else @maxResults end

    --generate output
    ;with myCTE (n,i) as 
    (
        --start at the beginning
        select @start
        , 1
        union all
        --increment in steps
        select n + @step
        , i + 1
        from myCTE 
        --ensure we've not overshot (accounting for direction of step)
        where (@maxResults=0 or i<@maxResults)
        and 
        (
               (@step > 0 and n <= @stop)
            or (@step < 0 and n >= @stop)
        )  
    )
    insert @results
    select n 
    from myCTE
    option (maxrecursion 0) --sadly we can't use a variable for this; however checks above should mean that we have a finite number of recursions / @maxResults gives users the ability to manually limit this 

    --all good  
    return

end

Поместите это для использования в вашем сценарии:

declare @start datetime = '2013-12-05 09:00'
       ,@end  datetime = '2014-03-02 13:00'

--get dates (midnight)
--, rounding <12:00 down to 00:00 same day, >=12:00 to 00:00 next day
--, incrementing by 1 day
select CAST(n as datetime)
from dbo.generate_series(cast(@start as bigint), cast(@end as bigint), default, default)

--get dates (start time)
--, incrementing by 1 day
select CAST(n/24.0 as datetime)
from dbo.generate_series(cast(@start as float)*24, cast(@end as float)*24, 24, default)

--get dates (start time)
--, incrementing by 1 hour
select CAST(n/24.0 as datetime)
from dbo.generate_series(cast(@start as float)*24, cast(@end as float)*24, default, default)

2005 Совместимость

Общие выражения таблицы: http://technet.microsoft.com/en-us/library/ms190766(v=sql.90).aspx
Опция MaxRecursion Подсказка: http://technet.microsoft.com/en-us/library/ms181714(v=sql.90).aspx
Табличные значения: http://technet.microsoft.com/en-us/library/ms191165(v=sql.90).aspx
Параметры по умолчанию: http://technet.microsoft.com/en-us/library/ms186755(v=sql.90).aspx
DateTime: http://technet.microsoft.com/en-us/library/ms187819(v=sql.90).aspx
Кастинг: http://technet.microsoft.com/en-us/library/aa226054(v=sql.90).aspx

Ответ 8

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

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

Ответ 9

Я использую следующее:

SELECT * FROM dbo.RangeDate(GETDATE(), DATEADD(d, 365, GETDATE()));

-- Generate a range of up to 65,536 contiguous DATES
CREATE FUNCTION dbo.RangeDate (   
    @date1 DATE = NULL
  , @date2 DATE = NULL
)   
RETURNS TABLE   
AS   
RETURN (
    SELECT D = DATEADD(d, A.N, CASE WHEN @date1 <= @date2 THEN @date1 ELSE @date2 END)
    FROM dbo.RangeSmallInt(0, ABS(DATEDIFF(d, @date1, @date2))) A
);

-- Generate a range of up to 65,536 contiguous BIGINTS
CREATE FUNCTION dbo.RangeSmallInt (
    @num1 BIGINT = NULL
  , @num2 BIGINT = NULL
)
RETURNS TABLE
AS
RETURN (
    WITH Numbers(N) AS (
        SELECT N FROM(VALUES
            (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 16
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 32
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 48
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 64
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 80
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 96
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 112
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 128
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 144
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 160
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 176
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 192
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 208
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 224
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 240
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 256
        ) V (N)
    )    
    SELECT TOP (
               CASE
                   WHEN @num1 IS NOT NULL AND @num2 IS NOT NULL THEN ABS(@num1 - @num2) + 1
                   ELSE 0
               END
           )
           ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) + CASE WHEN @num1 <= @num2 THEN @num1 ELSE @num2 END - 1
    FROM Numbers A
       , Numbers B
    WHERE ABS(@num1 - @num2) + 1 < 65537
);

Это не все, что отличается от многих предлагаемых решений, но есть несколько вещей, которые мне нравятся:

Нет необходимости в таблицах
Аргументы могут передаваться в любом порядке
Предел 65 536 дат произволен и может быть легко расширен путем замены на такую функцию, как RangeInt

Ответ 10

Это решение основано на изумительном ответе того же вопроса для MySQL. Это также очень хорошо работает на MSSQL. fooobar.com/questions/8819/...

select DateGenerator.DateValue from (
  select DATEADD(day, - (a.a + (10 * b.a) + (100 * c.a) + (1000 * d.a)), CONVERT(DATE, GETDATE()) ) as DateValue
  from (select a.a from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) as a(a)) as a
  cross join (select b.a from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) as b(a)) as b
  cross join (select c.a from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) as c(a)) as c
  cross join (select d.a from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) as d(a)) as d
) DateGenerator
WHERE DateGenerator.DateValue BETWEEN 'Mar 1 2009' AND 'Aug 1 2009'
ORDER BY DateGenerator.DateValue ASC

работает только для дат в прошлом, для дат в будущем изменении минус знак в функции DATEADD. Запрос работает только для SQL Server 2008+, но может быть переписан также в 2005 году, заменив конструкцию "select from values" на union.

Ответ 11

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

Ответ 12

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

Ответ 13

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

Итак, ответ от Devio был действительно полезен, но мне пришлось изменить его для работы в моей среде.

Если у вас нет доступа к master db, вы можете использовать другую таблицу в своей базе данных. Как и в предыдущем примере, максимальный диапазон дат определяется количеством строк внутри выбранной таблицы.

В моем примере жестко, используя row_number, вы можете использовать таблицы без фактического столбца int.

declare @bd datetime --begin date
declare @ed datetime --end date

set @bd = GETDATE()-50
set @ed = GETDATE()+5

select 
DATEADD(dd, 0, DATEDIFF(dd, 0, Data)) --date format without time
from 
(
    select 
    (GETDATE()- DATEDIFF(dd,@bd,GETDATE())) --Filter on the begin date
    -1 + ROW_NUMBER() over (ORDER BY [here_a_field]) AS Data 
    from [Table_With_Lot_Of_Rows]
) a 
where Data < (@ed + 1) --filter on the end date

Ответ 14

На самом деле, как решение Devio, поскольку мне нужно было что-то вроде этого, которое нужно запускать на SQL Server 2000 (так что нельзя использовать CTE), как бы он мог быть изменен, чтобы ТОЛЬКО генерировать даты, которые совпадают с заданным набором дней недели, Например, мне нужны только даты, которые совпадают с понедельником, средой и пятницей или любой конкретной выбранной мной последовательностью, основанной на следующем номере. Схема:

Sunday = 1
Monday = 2
Tuesday = 3
Wednesday = 4
Thursday = 5
Friday = 6
Saturday = 7

Пример:

StartDate = '2015-04-22' EndDate = '2017-04-22' --2 years worth
Filter on: 2,4,6 --Monday, Wednesday, Friday dates only

То, что я пытаюсь сделать, это добавить два дополнительных поля: day, day_code Затем отфильтруйте сгенерированный список с условием...

Я придумал следующее:

declare @dt datetime, @dtEnd datetime
set @dt = getdate()
set @dtEnd = dateadd(day, 1095, @dt)

select dateadd(day, number, @dt) as Date, DATENAME(DW, dateadd(day, number, @dt)) as Day_Name into #generated_dates
from 
    (select distinct number from master.dbo.spt_values
     where name is null
    ) n
where dateadd(day, number, @dt) < @dtEnd 

select * from #generated_dates where Day_Name in ('Saturday', 'Friday')

drop table #generated_dates

Ответ 15

Мне нравится CTE, так как его легко читать и обслуживать

Declare @mod_date_from date =getdate();
Declare @mod_date_to date =dateadd(year,1,@mod_date_from);

with cte_Dates as (
            SELECT @mod_date_from as reqDate
            UNION ALL
            SELECT DATEADD(DAY,1,reqDate)
            FROM cte_Dates
            WHERE DATEADD(DAY,1,reqDate) < @mod_date_to
        )
        SELECT * FROM cte_Dates
        OPTION(MAXRECURSION 0);

Не забудьте установить MAXRECURSION

Ответ 16

Это должно работать.

выберите Top 1000 DATEADD (d, ROW_NUMBER() OVER (ORDER BY Id), getdate()) из sysobjects