Мне нужно заполнить таблицу, которая будет хранить диапазоны дат между двумя заданными датами: 09/01/11 - 10/10/11
Итак, в этом случае таблица начнется с 09/01/11 и будет храниться каждый день, пока она не получит 10/10/11 Мне было интересно, если бы это было сделано в SQL Server - в настоящее время я использую SQL Server 2008. Спасибо.
Простота SQL 2005+; проще, если у вас есть цифры или таблица таблиц. Я подделал это ниже:
DECLARE @StartDate DATE = '20110901'
, @EndDate DATE = '20111001'
SELECT DATEADD(DAY, nbr - 1, @StartDate)
FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY c.object_id ) AS Nbr
FROM sys.columns c
) nbrs
WHERE nbr - 1 <= DATEDIFF(DAY, @StartDate, @EndDate)
Если у вас есть таблица подсчета, замените подзапрос на таблицу. Нет рекурсии.
Попробуйте это, если вы используете SQL Server 2005 или новее:
WITH Dates AS (
SELECT
[Date] = CONVERT(DATETIME,'09/01/2011')
UNION ALL SELECT
[Date] = DATEADD(DAY, 1, [Date])
FROM
Dates
WHERE
Date < '10/10/2011'
) SELECT
[Date]
FROM
Dates
OPTION (MAXRECURSION 45)
Хороший пример прохладного материала, который вы можете сделать с помощью CTE.
- Объявления
DECLARE @dates TABLE(dt datetime)
DECLARE @dateFrom datetime
DECLARE @dateTo datetime
SET @dateFrom = '2001/01/01'
SET @dateTo = '2001/01/12'
- Запрос:
WHILE(@dateFrom < @dateTo)
BEGIN
SELECT @dateFrom = DATEADD(day, 1,@dateFrom)
INSERT INTO @dates
SELECT @dateFrom
END
- Выход
SELECT * FROM @dates
Вот решение, которое не требует рекурсии, и в то же время эта табличная функция повторно используется во многих запросах без необходимости повторять объявление шаблонных переменных снова. Это единственная альтернатива для тех, кто не хочет рекурсии.
Создайте эту простую функцию:
CREATE FUNCTION [dbo].[GenerateDateRange]
(@StartDate AS DATE,
@EndDate AS DATE,
@Interval AS INT
)
RETURNS @Dates TABLE(DateValue DATE)
AS
BEGIN
DECLARE @CUR_DATE DATE
SET @CUR_DATE = @StartDate
WHILE @CUR_DATE <= @EndDate BEGIN
INSERT INTO @Dates VALUES(@CUR_DATE)
SET @CUR_DATE = DATEADD(DAY, @Interval, @CUR_DATE)
END
RETURN;
END;
И затем выберите по:
select *
from dbo.GenerateDateRange('2017-01-03', '2017-12-01', 1)
Используйте функцию MVJ F_TABLE_DATE, это просто потрясающе:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519
Как только вы реализуете этот простой проход в начале и в конце даты, и вы можете вставить все даты между ними.
Используя @Abe Miesler ответ, для другого удобства я построил его в TVF для SQL Server 2008 и далее. Это может помочь другим - мне нужно найти способ включить CTE в TVF!
--Generate a range of dates with interval option, courtesy of Abe Miessler for the core query here!
ALTER FUNCTION [dbo].[DateRange]
(@startDate AS DATE,
@EndDate AS DATE,
@interval AS INT
)
RETURNS @Dates TABLE(dateValue DATE)
AS
BEGIN
WITH Dates
AS (
SELECT [Date] = CONVERT( DATETIME, @startDate)
UNION ALL
SELECT [Date] = DATEADD(DAY, ISNULL(@interval, 1), [Date])
FROM Dates
WHERE Date < @EndDate)
INSERT INTO @Dates
SELECT [Date]
FROM Dates
OPTION(MAXRECURSION 900);
RETURN;
END;
Declare @StartDate datetime = '2015-01-01'
Declare @EndDate datetime = '2016-12-01'
declare @DaysInMonth int
declare @tempDateRange Table
(
DateFrom datetime,
DateThru datetime
);
While @StartDate<[email protected]
begin
SET @DaysInMonth=DAY(DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,-1,@StartDate),0)))
IF DAY(@StartDate)=1
SET @EndDate=DATEADD(DAY,14,@StartDate)
ELSE IF DAY(@StartDate)=16 AND @DaysInMonth=30
SET @EndDate=DATEADD(DAY,14,@StartDate)
ELSE IF DAY(@StartDate)=16 AND @DaysInMonth=31
SET @EndDate=DATEADD(DAY,15,@StartDate)
ELSE IF DAY(@StartDate)=16 AND @DaysInMonth=28
SET @EndDate=DATEADD(DAY,12,@StartDate)
ELSE IF DAY(@StartDate)=16 AND @DaysInMonth=29
SET @EndDate=DATEADD(DAY,13,@StartDate)
INSERT INTO @tempDateRange (DateFrom,DateThru)
VALUES
(
@StartDate,
@EndDate
)
SET @StartDate=DATEADD(DAY,1,@EndDate)
IF @EndDate< '2016-12-31'
IF DAY(@StartDate)=1
SET @EndDate=DATEADD(DAY,14,@StartDate)
ELSE IF DAY(@StartDate)=16 AND @DaysInMonth=30
SET @EndDate=DATEADD(DAY,14,@StartDate)
ELSE IF DAY(@StartDate)=16 AND @DaysInMonth=31
SET @EndDate=DATEADD(DAY,15,@StartDate)
ELSE IF DAY(@StartDate)=16 AND @DaysInMonth=28
SET @EndDate=DATEADD(DAY,12,@StartDate)
ELSE IF DAY(@StartDate)=16 AND @DaysInMonth=29
SET @EndDate=DATEADD(DAY,13,@StartDate)
end ;
select * from @tempDateRange
+++++++++++++++++++++++++++++
Result:
DateFrom |DateThru
Если по какой-то причине вы не можете использовать переменные declare, например, при использовании производных таблиц в Looker, вы можете сделать следующее:
select
dateadd(day, nbr - 1, convert(date, '2017-01-01')) as d
from (
select row_number() over (order by c.object_id) as nbr from sys.columns c
) nbrs
where
nbr - 1 <= datediff(
day,
convert(date, '2017-01-01'),
convert(date, '2018-12-31')
)
Кстати, вот так может выглядеть ваша серия дат в LookerML:
view: date_series {
derived_table: {
sql:
select
dateadd(day, nbr - 1, convert(date, '2017-01-01')) as d
from (
select row_number() over (order by c.object_id) as nbr from sys.columns c
) nbrs
where
nbr - 1 <= datediff(day, convert(date, '2017-01-01'), convert(date, '2018-12-31')) ;;
}
dimension: date {
primary_key: yes
type: date
sql: ${TABLE}.d ;;
}
}
CREATE table #ProductSales (ProjectID Int, ProjectName varchar(100), TotalBillableFees Money, StartDate Date, EndDate Date, DataDate Date)
Insert into #ProductSales
Values
(373104,'Product Sales - Flex Creation Test',40000.00,'2019-04-01','2020-06-01','2019-08-01'),
(375111,'Product Sales - SMART',40000.00,'2019-04-01','2019-09-01','2019-08-01')
;WITH Dates AS (
SELECT ProjectiD
,Convert(decimal(10,2),TotalBillableFees/IIF(DATEDIFF(MONTH,StartDate,EndDate)=0,1,DATEDIFF(MONTH,StartDate,EndDate))) AS BillableFeesPerMonths,EndDate
,[Date] = CONVERT(DATETIME,EOMONTH(StartDate))
FROM #ProductSales
UNION ALL SELECT ProjectiD,BillableFeesPerMonths,EndDate,
[Date] = DATEADD(MONTH, 1, [Date])
FROM
Dates
WHERE
Date < EOMONTH(EndDate)
) SELECT ProjectID,BillableFeesPerMonths,
CAST([Date] as Date) Date
FROM
Dates
OPTION (MAXRECURSION 45)
Я понимаю, что это старая тема, но я должен признать, что смутился из-за переизбытка рекурсивных и циклических решений, приведенных здесь. Интересно, сколько людей понимают, что рекурсия - не что иное, как очень дорогой цикл? Я понимаю желание создать табличную функцию, но я полагаю, что следующее гораздо более эффективно, поскольку оно основано на множествах, без зацикливания, рекурсии или повторных одиночных операторов вставки:
CREATE FUNCTION dbo.GenerateDateRange(@StartDate AS DATE, @EndDate AS DATE)
RETURNS TABLE WITH SCHEMABINDING AS
WITH e1(n) AS (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS x(n)) -- 16 records
,e2(n) AS (SELECT 1 FROM e1 a CROSS JOIN e1 b) -- 16^2 or 256 records (16*16)
,cteTally(n) AS (SELECT ROW_NUMBER() over (ORDER BY 1) AS n FROM e2 a CROSS JOIN e2 b) -- 16^4 or 65,536 records (256*256)
SELECT DATEADD(DAY, n-1, @StartDate)
FROM cteTally
WHERE n <= DATEDIFF(DAY, @StartDate, @EndDate) + 1;
GO