SQL - получить конкретные даты в зависимости от рабочей недели

Нам нужно определить конкретные даты, между которыми будут извлечены данные.

Например, проводки, которые происходят в первое число месяца, разносятся в систему бухгалтерского учета 2-го числа, а проводки, которые происходят в последний день месяца, разносятся в систему бухгалтерского учета 1-го числа следующего месяца.

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

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

Declare @EarliestDate varchar(8), @SQL NVARCHAR(1000), @sDate varchar(8)

-- get data for the last 3 months
SET @EarliestDate= CAST(DATEPART(YEAR,DATEADD(m,-3, getdate())) AS VARCHAR(4)) 
    + RIGHT('00' + CAST(DATEPART(mm, DATEADD(m,-2, getdate())) AS varchar(2)), 2)+ '02'

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

Я запустил приведенный ниже код, но не могу заставить его работать должным образом:

Declare @LatestDate datetime

set @LatestDate = '01/11/2014'

if datepart(dw,@LatestDate) in (1,7)
Begin
    set @LatestDate = DATEADD(d,1,@LatestDate ) 
End

Пожалуйста, покажите мне, где я ошибаюсь? В идеале он должен работать круглый год независимо от года, всегда получая первый рабочий день после 1-го числа месяца.


tsql dateadd sql-server-2008-r2 datepart
person Our Man in Bananas    schedule 05.01.2015    source источник
comment
Если это вариант, вы можете рассмотреть возможность добавления нового столбца метаданных, который заполняется с помощью триггера, а затем просто запросить этот столбец. Кажется, что здесь задействовано больше бизнес-логики, чем вы даже думаете. Праздники - проблема?   -  person Lynn Crumbling    schedule 05.01.2015
comment
@LynnCrumbling: да, мне только что напомнили, что праздники действительно будут проблемой, поэтому придется подумать о том, чтобы проверить, есть ли дата в таблице праздников ...   -  person Our Man in Bananas    schedule 05.01.2015
comment
Я могу почти гарантировать вам, что когда-нибудь кому-то еще понадобится запрос на основе Post_Date. Это действительно похоже на дополнительный столбец, который должно быть заполнено приложением или триггером, который запускается при вставке / обновлении строки.   -  person Lynn Crumbling    schedule 05.01.2015
comment
@LynnCrumbling: ну, это для отчета SSRS, поэтому я дам пользователям возможность вводить диапазон дат, но если они не вводят диапазон дат (например, для запланированного выполнения), то это всегда будут месяцы данные с 1-го рабочего дня после первого до 1-го рабочего дня следующего месяца   -  person Our Man in Bananas    schedule 05.01.2015

Ответы (2)


arrow_upward
1
arrow_downward

Я рекомендую вам создать таблицу дат из сценария по адресу Создайте и заполните измерение" Дата "для хранилища данных и используйте его, чтобы определить, когда наступит ваш следующий рабочий день. Для своих целей я отбросил все столбцы Великобритании и Фискальные столбцы - хотя они могут быть вам полезны. Вы также можете добавить свои собственные праздники и присоединиться непосредственно к запросу вместо создания определенной функции.

Тогда вы можете просто сделать следующее:

DECLARE @LatestDate date

SET @LatestDate = '01/11/2014'

SELECT @LatestDate = MIN([Date]) FROM DimDate
WHERE [Date] >= @LatestDate
AND IsWeekday = 1 AND IsHoliday = 0

PRINT @LatestDate
person Daniel Gimenez    schedule 05.01.2015
comment
По сути, это ответ только для ссылки; Большая часть контента, который вы просите OP использовать в качестве решения, находится на другом веб-сайте. - person Lynn Crumbling; 05.01.2015
comment
Спасибо, Дэниел, я вынул все столбцы и даты праздников США, и это работает ... - person Our Man in Bananas; 05.01.2015

arrow_upward
1
arrow_downward

Здесь нужно учесть несколько моментов:

Используйте линию

Set Dateformat dmy;

Чтобы преобразование строки в дату было в правильном формате даты (в данном случае не в американском).

Убедитесь, что (1,7) - правильные даты выходных для конкретного сервера. Вы можете проверить это, запустив:

Select @@Datefirst

Что подскажет, какой день недели первый. По умолчанию 7 (воскресенье), что работает для примера ниже. При необходимости вы можете изменить это:

http://msdn.microsoft.com/en-gb/library/ms181598.aspx

Об этом следует помнить, если вы планируете переносить код на другой сервер.

Ваш код должен добавить 1 для воскресенья и 2 для субботы:

-- Saturday
if datepart(dw,@LatestDate) = (1)
    set @LatestDate = DATEADD(dd,1,@LatestDate);

-- Sunday
if datepart(dw,@LatestDate) = (7)
    set @LatestDate = DATEADD(dd,2,@LatestDate);

Использование измерения даты и добавление небольших начальных накладных расходов проще, эффективнее и надежнее (т.е. независимо от @@ Datefirst). Вы также можете легко указать государственные праздники и учесть их. Существует множество скриптов для заполнения таблицы измерения даты, например:

http://www.codeproject.com/Articles/647950/Create-and-Populate-Date-Dimension-for-Data-Wareho

person SQLDiver    schedule 05.01.2015