Идея здесь заключается в том, что плата будет взиматься с четвертого дня после даты выставления счета.
Следующий код работает, но мне просто интересно, есть ли более эффективный способ добиться этого, поскольку я мог думать только об использовании курсора, чтобы получить свой четвертый день. Со временем это станет функцией. Я передам дату (но используя 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
он работает, как и ожидалось, но я чувствую, что использование курсора плохо?
Вы также можете использовать DATEPART (день недели... здесь может работать немного логики, основанной на начальном дне
Первое, что я хотел бы сказать, это получить себе таблицу календаря, они действительно полезны и являются практически первым, что я добавляю в любую новую базу данных, которую я создаю. Тогда для всех счетов ваш запрос будет таким же простым, как:
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 Да, скопировал код, не подумав. я обновил ответ
о ничего себе - стол-календарь! какая отличная идея, я полностью делаю это. Я также не рассматривал перенос государственного праздника на понедельник, если он выпадает на выходные. Большое спасибо всем за все ваши советы.
Конечно (просто чтобы добавить к моему пятничному дню) в Мексике: когда установленный законом выходной день выпадает на воскресенье, понедельник считается установленным законом праздником; если установленный законом выходной день приходится на субботу, пятница будет считаться официальным выходным днем. :-)
Таблица календаря всегда может быть расширена, чтобы флаг «IsWorkingDay» фактически вычислялся на основе дочерних таблиц, один из таких примеров — в этой статье. Я бы лично построил это немного по-другому, но это показывает суть. Затем у вас может быть функция с табличным значением, которая возвращает календарь на основе кода страны с отмеченными конкретными рабочими днями, например. SELECT * FROM dbo.CountryCalendar('MX')
чтобы получить праздники, характерные для Мексики.
Это другой подход, не лучше принятого ответа , но другой:
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-й рабочий день до сегодняшнего дня.
Курсор не требуется, вам нужна таблица календаря.