Возврат на 4-й рабочий день (исключая праздничные дни) с указанной даты

Идея здесь заключается в том, что плата будет взиматься с четвертого дня после даты выставления счета.

Следующий код работает, но мне просто интересно, есть ли более эффективный способ добиться этого, поскольку я мог думать только об использовании курсора, чтобы получить свой четвертый день. Со временем это станет функцией. Я передам дату (но используя getdate() в качестве примера здесь) и верну 4-й рабочий день. NB: Рабочий день не включает субботу и воскресенье, а также праздничные дни. У меня есть таблица государственных праздников.

DECLARE @test_date AS DATETIME, @test_day_number as int

DECLARE billdate CURSOR FOR 
WITH DateSequence AS (
    SELECT 
        DATEADD(DAY, num, GETDATE()) AS target_date,
        DATEPART(WEEKDAY, DATEADD(DAY, num, GETDATE())) AS day_of_week
    FROM (
        VALUES (1), (2), (3), (4), (5), (6), (7), (8)
    ) AS nums(num)
)
    SELECT 
        ds.target_date AS dates_after_target,
        ROW_NUMBER() OVER (ORDER BY ds.target_date) AS day_number
    FROM
        DateSequence ds
    WHERE
        ds.day_of_week NOT IN (1, 7)  -- Exclude Sunday (1) and Saturday (7)
        AND ds.target_date NOT IN (SELECT hdate FROM holidays) -- exclude holiday dates
    ORDER BY
        ds.target_date
OPEN billdate
FETCH NEXT FROM billdate INTO @test_date, @test_day_number
WHILE @@FETCH_STATUS = 0  
begin 
    if @test_day_number=4 
        break 
    else 
        FETCH NEXT FROM billdate INTO @test_date, @test_day_number
end 

close billdate
deallocate billdate
print @test_date

он работает, как и ожидалось, но я чувствую, что использование курсора плохо?

Курсор не требуется, вам нужна таблица календаря.

Stu 18.08.2023 13:49

Вы также можете использовать DATEPART (день недели... здесь может работать немного логики, основанной на начальном дне

Nathan_Sav 18.08.2023 14:00
ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
0
2
50
2
Перейти к ответу Данный вопрос помечен как решенный

Ответы 2

Ответ принят как подходящий

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

WITH WorkingDays AS
(   SELECT c.Date, RowNumber = ROW_NUMBER() OVER(ORDER BY c.Date) 
    FROM dbo.Calendar AS c
    WHERE c.IsWorkingDay = 1
)
SELECT  i.InvoiceID, i.InvoiceDate, wd2.Date
FROM    dbo.Invoice AS i
        INNER JOIN WorkingDays AS wd1
            ON wd1.Date = i.InvoiceDate
        INNER JOIN WorkingDays AS wd2
            ON wd2.RowNumber = wd1.RowNumber + 4;

Это может совсем не соответствовать вашей схеме, но, надеюсь, дает достаточно идеи, чтобы понять суть этого.

Чтобы ответить на ваш вопрос, хотя вы правы, ему определенно не нужен курсор, но вам понадобится более 7 дней в вашей последовательности, потому что различные праздники могут потребовать более 7 календарных дней, чтобы составить 4 рабочих дня ( например, в Великобритании 4 рабочих дня после 22 декабря — это 2 января, то есть на 11 календарных дней позже). Если вам просто нужен день на 4 рабочих дня раньше, вы можете просто сделать:

WITH DateSequence AS (
    SELECT 
        DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY n1.n), CONVERT(DATE, GETDATE())) AS target_date
    FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS n1(n)
    CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS n2(n)
)
SELECT  ds.target_date
FROM    DateSequence AS ds
WHERE   NOT EXISTS (SELECT 1 FROM Holidays AS h WHERE h.Date = ds.Target_date)
AND     (DATEPART(WEEKDAY, ds.target_date) + @@DATEFIRST) % 7 NOT IN (0, 1)
ORDER BY ds.target_date
OFFSET 3 ROWS FETCH NEXT 1 ROW ONLY;

Это просто получает следующие 100 дней после сегодняшнего дня, затем исключает выходные и даты из вашей праздничной таблицы, а затем использует OFFSET/FETCH для получения 4-й строки.

Н.Б. Я заменил вашу логику рабочих дней на (DATEPART(WEEKDAY, ds.target_date) + @@DATEFIRST) % 7 NOT IN (1, 2). Если вы просто используете DATEPART(WEEKDAY, <date>), вы подвержены проблемам, возникающим, если DATEFIRST не соответствует вашим ожиданиям. Таким образом, если бы DATEFIRST было равно 3, это исключило бы вторник и среду, а не субботу и воскресенье. пример на db<>fiddle

Однако, как я уже сказал, все это намного проще с календарной таблицей:

SELECT  c.Date
FROM    dbo.Calendar AS c
WHERE   c.Date > GETDATE()
AND     c.IsWorkingDay = 1
ORDER BY c.Date 
OFFSET 3 ROWS FETCH NEXT 1 ROW ONLY;
( @@DateFirst + DatePart( weekday, SampleDate ) - 1 ) % 7 + 1 всегда будет возвращать целое число от 1 до 7, где 1 соответствует воскресенью, независимо от настройки DateFirst или Language. Ваше выражение ((DATEPART(WEEKDAY, ds.target_date) + @@DATEFIRST) % 7) отсчитывается от нуля, поэтому выходные будут ( 0, 1 ), а не ( 1, 2 ).
HABO 18.08.2023 14:49

@HABO Да, скопировал код, не подумав. я обновил ответ

GarethD 18.08.2023 14:59

о ничего себе - стол-календарь! какая отличная идея, я полностью делаю это. Я также не рассматривал перенос государственного праздника на понедельник, если он выпадает на выходные. Большое спасибо всем за все ваши советы.

limpet 18.08.2023 17:02

Конечно (просто чтобы добавить к моему пятничному дню) в Мексике: когда установленный законом выходной день выпадает на воскресенье, понедельник считается установленным законом праздником; если установленный законом выходной день приходится на субботу, пятница будет считаться официальным выходным днем. :-)

limpet 18.08.2023 17:29

Таблица календаря всегда может быть расширена, чтобы флаг «IsWorkingDay» фактически вычислялся на основе дочерних таблиц, один из таких примеров — в этой статье. Я бы лично построил это немного по-другому, но это показывает суть. Затем у вас может быть функция с табличным значением, которая возвращает календарь на основе кода страны с отмеченными конкретными рабочими днями, например. SELECT * FROM dbo.CountryCalendar('MX') чтобы получить праздники, характерные для Мексики.

GarethD 18.08.2023 20:11

Это другой подход, не лучше принятого ответа , но другой:

CREATE Function BusinessDay(@day date, @wd smallint = 4)
returns date as
begin
    while @wd <> 0
    begin
        set @day  = dateAdd(dd, sign(@wd), @day)
        set @wd  -= case when exists (select 1 from Holidays where HDate = @day)
                        or ((@@DATEFIRST + datePart(dw, @day))%7 in (0, 1))
                        then 0 else sign(@wd)
                    end
    end
    return @day
end

Вы можете попросить dbo.BusinessDay(getDate(), DEFAULT) получить 4-й рабочий день после сегодняшнего дня или dbo.BusinessDay(getDate(), -4) за 4-й рабочий день до сегодняшнего дня.

Другие вопросы по теме