Список всех рабочих дат между двумя датами в SQL

у меня есть код SQL для создания списка дат между двумя датами, но я хочу сгенерировать дни недели (рабочие дни) из заданных двух дат,

DECLARE @MinDate DATE = '20140101', @MaxDate DATE = '20140106';
SELECT TOP (DATEDIFF(DAY, @MinDate, @MaxDate) + 1)
    Date = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1,@MinDate)
FROM sys.all_objects a
CROSS JOIN sys.all_objects b;

Это мой код, поэтому кто-нибудь, пожалуйста, предложите мне получить список будних дней в одиночку. В онлайн-источниках есть код для определения количества дней, а не для перечисления всех дат, в этом и заключается мое замешательство.


sql sql-server sql-server-2008 sql-server-2012-express
person user1799596    schedule 11.03.2015    source источник
comment
хороший способ найти рабочие дни уже был предложен здесь   -  person Sim1    schedule 11.03.2015
comment
Из каждой недели вычесть два дня?   -  person jarlh    schedule 11.03.2015

Ответы (4)


arrow_upward
8
arrow_downward

Попробуй это:

DECLARE @MinDate DATE = '20140101',
        @MaxDate DATE = '20140106'

;WITH N1 (N) AS (SELECT 1 FROM (VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) n (N)),
N2 (N) AS (SELECT 1 FROM N1 AS N1 CROSS JOIN N1 AS N2),
N3 (N) AS (SELECT 1 FROM N2 AS N1 CROSS JOIN N2 AS N2),
N4 (N) AS (SELECT ROW_NUMBER() OVER(ORDER BY N1.N) FROM N3 AS N1 CROSS JOIN N3 AS N2)
SELECT  Date = DATEADD(DAY, N - 1, @MinDate)
FROM    N4
WHERE 
  N < DATEDIFF(DAY, @MinDate, @MaxDate) + 2 AND
  DATEDIFF(DAY, 1 - N, @MinDate) % 7 NOT IN (5,6)

Результат:

Date
2014-01-01
2014-01-02
2014-01-03
2014-01-06
person t-clausen.dk    schedule 11.03.2015
comment
использование модуля 7 в DATEDIFF(d,1 - N, @MinDate) % 7 NOT IN (5,6) надежно независимо от настроек сервера sql и не зависит от языка. - person Paul Maxwell; 11.03.2015
comment
@Used_By_Already: не модуль 7 делает этот запрос независимым от настроек, а неявное преобразование int в datetime. Проблема, однако, в том, что это решение имеет ошибку. Если вы попытаетесь изменить @MinDate на 20140102, вы получите неправильный результат. Возможно, t-clausen.dk имел в виду эту версию DATEDIFF: DATEDIFF(DAY, 0, DATEADD(DAY, N - 1, @MinDate)) % 7 NOT IN (5,6). - person Andriy M; 13.03.2015
comment
Я имел в виду не причину независимости настроек, а использование по модулю 7. Я пытался описать, что сделал % 7 и предложил в качестве преимущества (возможно, плохого), то есть результат (5,6) не зависит от настроек . Возможно, вы действительно правы в отношении необходимости этого dateadd(), я позволю автору ответить на это. Ваше здоровье. - person Paul Maxwell; 13.03.2015

arrow_upward
6
arrow_downward

Сделайте исходный запрос как sub-select, который генерирует все даты между двумя заданными датами, затем выполните фильтрацию в outer query.

SET DATEFIRST 1

select [Date] from
(
SELECT  TOP (DATEDIFF(DAY, @MinDate, @MaxDate) + 1)
    Date = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1,@MinDate)
FROM    sys.all_objects a
    CROSS JOIN sys.all_objects b
)
where datename(dw,[Date]) not in ('Saturday','Sunday')
person Pரதீப்    schedule 11.03.2015
comment
безупречно, за исключением того, что по какой-то причине мой проводник SQL не позволяет таблице быть таблицей. - person CashCow; 18.12.2017
comment
Set DateFirst не влияет на функции DateDiff, DateAdd или DateName в вашем коде. Эту строку можно удалить без каких-либо последствий. Также вам нужно указать подразумеваемую таблицу в () псевдонимом, иначе она не будет работать в SQL Server (например, ... Cross Join sys.all_objects b) MyTemp where datename(.... - person Jeff Winchell; 16.04.2020

arrow_upward
2
arrow_downward

Давным-давно я построил таблицу-календарь, чтобы отвечать на такие вопросы, как ваш. Основным преимуществом, помимо простоты использования, является то, что вы можете посмотреть на запрос в таблице календаря и сказать: «Это очевидно верно».

select cal_date, day_of_week
from calendar
where day_of_week in ('Mon', 'Tue', 'Wed', 'Thu', 'Fri')
  and cal_date between '2014-01-01' and '2014-01-06'
order by cal_date;

cal_date    day_of_week
--
2014-01-01  Wed
2014-01-02  Thu
2014-01-03  Fri
2014-01-06  Mon

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

select cal_date, day_of_week
from weekdays
where cal_date between '2014-01-01' and '2014-01-06'
order by cal_date;
person Mike Sherrill 'Cat Recall'    schedule 11.03.2015
comment
как создать таблицу календаря? - person user1799596; 11.03.2015
comment
Вот пример таблицы календаря с использованием PostgreSQL. Адаптироваться к SQL Server должно быть довольно легко, тем более что вам нужны только два столбца. - person Mike Sherrill 'Cat Recall'; 11.03.2015

arrow_upward

arrow_downward

person    schedule
comment
Спасибо за ответ, Мэтт, но выходные (суббота и воскресенье) не фильтруются. Мне не нужны даты выходных в моем коде. - person user1799596; 11.03.2015
comment
При сравнении с проверкой субботы и воскресенья datepart ненадежен, это зависит от настройки datefirst, которая по умолчанию может отличаться от базы данных к базе данных. - person t-clausen.dk; 11.03.2015
comment
Да, я думал, что это может быть, я удалю вторую версию - person Matt; 11.03.2015
comment
по-прежнему я не получаю правильный вывод, например, если я передаю это значение как дату начала и дату окончания «2015/12/13», «2015/12/27», даты не генерируются. - person user1799596; 11.03.2015
comment
Поскольку ваша дата начала и дата окончания являются выходными, если вы их используете, они не будут работать, поскольку они не включены, попробуйте «2015/12/14» и «2015/12/25». - person Matt; 11.03.2015