Рассмотрим необходимость создания набора дат. У нас есть даты начала и окончания, и мы хотели бы создать список дат между ними.
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, отметьте как таковой.
Если ваши даты не более чем на 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
В следующем случае используется рекурсивный 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)
Как только таблица 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)
@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.
Попробуйте это. 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
Обратите внимание, что вложение предназначено для упрощения управления и преобразования в представления и т.д.
Другой вариант - создать соответствующую функцию в .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 заключается в том, что она не создает временную таблицу.
Обзор
Здесь моя версия (совместимая с 2005 годом). Преимущества этого подхода заключаются в следующем:
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 Совместимость
создать временную таблицу с целыми числами от 0 до разницы между двумя датами.
SELECT DATE_ADD(@Start, INTERVAL tmp_int DAY) AS the_date FROM int_table;
Я использую следующее:
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
);
Это не все, что отличается от многих предлагаемых решений, но есть несколько вещей, которые мне нравятся:
Это решение основано на изумительном ответе того же вопроса для 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.
Я бы рекомендовал: создать вспомогательную таблицу чисел и использовать ее для создания списка дат. Вы также можете использовать рекурсивный CTE, но это может не работать, а также присоединяться к вспомогательной таблице чисел. См. SQL, Вспомогательная таблица чисел для получения информации обо всех параметрах.
В то время как мне действительно нравится решение KM выше (+1), я должен опросить ваше предположение "no loop" - учитывая вероятные диапазоны дат, с которыми ваше приложение будет работать, причем цикл не должен быть действительно таким дорогим. Основной трюк состоит в том, чтобы разбить результаты цикла в таблице промежуточного/кэша, так что чрезвычайно большие наборы запросов не замедляют работу системы, пересчитывая одни и те же точные даты. Например. каждый запрос только вычисляет/кэширует диапазоны дат, которые НЕ уже находятся в кеше и что им нужно (и предварительно заполняет таблицу с некоторым реалистичным диапазоном дат, например, за 2 года вперед, с диапазоном, определенным потребностями вашего приложения).
Лучший ответ, вероятно, заключается в использовании 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
На самом деле, как решение 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
Мне нравится 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
Это должно работать.
выберите Top 1000 DATEADD (d, ROW_NUMBER() OVER (ORDER BY Id), getdate()) из sysobjects