Хранимая процедура для принятия даты начала и количества последовательных дат, начинающихся с даты начала

Question

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

Затем хранимая процедура должна заполнить все столбцы таблицы DateRange в соответствии с двумя предоставленными параметрами.

Я создал таблицу:

CREATE TABLE DateRange
(
    DateID INT IDENTITY,
    DateValue DATE,
    Year INT,
    Quarter INT,
    Month INT,
    DayOfWeek INT
);

Код хранимой процедуры:

CREATE FUNCTION dbo.DateRange_sp4
    (@StartDate DATE,
     @NumberofConsecutivedays INT)
RETURNS @DateList TABLE 
                  (
                       DateID INT, 
                       DateValue DATE,
                       Year INT, 
                       Quarter INT, 
                       Month INT, 
                       DayOfWeek INT
                  )
AS 
BEGIN
    DECLARE @Counter INT = 0;

    WHILE (@Counter < @NumberofConsecutivedays)
    BEGIN
        INSERT INTO @DateList
        VALUES (@Counter + 1, 
                DATEADD(DAY, @Counter, @StartDate), 
                DATEPART(YEAR, @StartDate), 
                DATEPART(QUARTER, @StartDate),
                DATEPART(MONTH, @StartDate), 
                DatePart(WEEKDAY, @StartDate) );

        SET @Counter += 1
    END

    RETURN;
END
GO

SELECT * 
FROM dbo.DateRange_sp4('2018-07-13', 20);

Мой вывод возвращает тот же результат для года, квартала, месяца и дня недели. Как разбить дату на разные столбцы? Или есть другой способ сделать это?

Answer 1

Используйте таблицу подсчета... это будет намного быстрее. Проверьте это на 10 000 дней... и запустите код цикла на 10 000 дней.

declare @dateparameter date = '1900-04-12'
declare @numOfDays int = 10000

;WITH
    E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
    E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
    E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
    cteTally(N) AS 
    (
        SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
    )

select 
    datevalue = @dateparameter
    ,year = datepart(year,@dateparameter)
    ,quarter = datepart(quarter,@dateparameter)
    ,month = datepart(month,@dateparameter)
    ,dayofweek = datepart(weekday,@dateparameter)
union all
select 
    datevalue = dateadd(day,N,@dateparameter)
    ,year = datepart(year,dateadd(day,N,@dateparameter))
    ,quarter = datepart(quarter,dateadd(day,N,@dateparameter))
    ,month = datepart(month,dateadd(day,N,@dateparameter))
    ,dayofweek = datepart(weekday,dateadd(day,N,@dateparameter))
from cteTally
where N <= @numOfDays

Но, если вы собираетесь ссылаться на это много, почему бы не сделать постоянную таблицу? У Аарона Бертрана есть отличная статья на эту тему: https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/

Answer 2

Вы используете одно и то же значение для @StartDate при каждой итерации цикла.

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

INSERT INTO @DateList
VALUES(@Counter + 1, DATEADD(day,@Counter, @StartDate), DATEPART(year, @StartDate), DATEPART(QUARTER, @StartDate),DATEPART(month, @StartDate), DatePart(WEEKDAY,@StartDate)) ;
SET @StartDate = DATEADD(day,@Counter + 1, @StartDate);
SET @Counter +=1;