sql количество рабочих дней

Originally posted by kasap
Большое спасибо dragonv!

Преобразуй это в функцию
CREATE FUNCTION dbo.day_notholiday_1 (@dat_n datetime, @dat_f datetime)
RETURNS int
AS
BEGIN
declare @count int
declare @dat_dif datetime
set @count=0
set @dat_dif=@dat_n

Originally posted by dragonv
Преобразуй это в функцию
CREATE FUNCTION dbo.day_notholiday_1 (@dat_n datetime, @dat_f datetime)
RETURNS int
AS
BEGIN
declare @count int
declare @dat_dif datetime
set @count=0
set @dat_dif=@dat_n

Вот так будет правильно показывать все дни. Функция Datediff в данном случае не совсем подходит,так как считает разницу, но не включительно (будь осторожнее с этой функцией. ).

select
waybill_num as ‘Номер накладной,
convert(char, date_of_depart, 102) as ‘Дата отправки’,
convert(char, date_of_rec, 102) as ‘Дата получения’,
DATEDIFF(dd, date_of_depart, date_of_rec)+1 as ‘Срок доставки в календарных днях’,
dbo.day_notholiday_1(date_of_depart, date_of_rec) as ‘Срок доставки в рабочих днях’
from way_bills

if exists (select * from dbo.sysobjects where and OBJECTPROPERTY(id, N’IsUserTable’) = 1)
drop table [dbo].[Holiday]
GO

Источник

Количество рабочих дней между двумя датами

Как рассчитать количество рабочих дней между двумя датами в SQL Server?

С понедельника по пятницу, и это должен быть T-SQL.

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

Если вы хотите включить праздники, вам нужно немного поработать …

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

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

Все кредиты Богдану Максиму и Питеру Мортенсену. Это их сообщение, я просто добавил праздники к функции (Это предполагает, что у вас есть таблица «tblHolidays» с полем «дата-время» «HolDate».

Во всяком случае, вот моя деловая функция

(Я немного застенчивый от комментариев)

мое довольно упрощенное решение:

.. который также устанавливает все ошибочные сообщения с датой начала после окончания даты до нуля. Что-то, что вы можете или не ищете.

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

Хотя опция цикла WHILE является более чистой и использует меньше строк кода, у нее есть потенциал быть узким местом производительности в вашей среде, особенно когда ваш диапазон дат охватывает несколько лет.

Вы можете увидеть больше методов расчета рабочих дней и часов в этой статье: https://www.sqlshack.com/how-to-calculate-work-days-and-hours-in-sql-server/

Для разницы между датами, включая праздники, я пошел так:

1) Таблица с праздниками:

2) У меня была таблица с планами как это, и я хотел заполнить столбец Work_Days, который был пуст:

3) Итак, чтобы получить «Work_Days», чтобы позже заполнить мою колонку, нужно было:

Надеюсь, что я могу помочь.

Вот версия, которая работает хорошо (я думаю). Таблица праздников содержит столбцы Holiday_date, которые содержат праздники, которые ваша компания соблюдает.

Использование таблицы дат:

Если у вас этого нет, вы можете использовать таблицу чисел:

Они должны быть быстрыми, и это устраняет двусмысленность / сложность. Первый вариант является лучшим, но если у вас нет таблицы календаря, вы всегда можете создать таблицу чисел с CTE.

Я привел различные примеры здесь, но в моей конкретной ситуации у нас есть @PromisedDate для доставки и @ReceivedDate для фактического получения элемента. Когда элемент был получен до «PromisedDate», расчеты не были правильными, если я не заказывал даты, переданные в функцию по календарному заказу. Не желая проверять даты каждый раз, я изменил функцию, чтобы обработать это для меня.

Если вам нужно добавить рабочие дни к указанной дате, вы можете создать функцию, которая зависит от таблицы календаря, описанной ниже:

Более чистый способ борьбы с субботой / воскресенью – перевести даты до извлечения значения дня недели. После смещения значения вернутся в соответствие с фиксированной (и, вероятно, более привычной) нумерацией, которая начинается с 1 в воскресенье и заканчивается 7 в субботу.

Я проследил эту форму решения, по крайней мере, до 2002 года и статью Itzik Ben-Gan. ( https://technet.microsoft.com/en-us/library/aa175781(v=sql.80).aspx ) Хотя для этого нужна небольшая настройка, так как новые типы date не позволяют арифметику даты, она в остальном идентична.

Это работает на меня, в моей стране в субботу и воскресенье – нерабочие дни.

Для меня важно время @StartDate и @EndDate.

Создайте функцию, например:

Вы можете вызвать функцию:

Я нашел ниже TSQL довольно элегантное решение (у меня нет разрешений на запуск функций). Я обнаружил, что DATEDIFF игнорирует DATEFIRST и я хотел, чтобы мой первый день недели был понедельником. Я также хотел, чтобы первый рабочий день был установлен на ноль, и если он выпадает на выходные в понедельник, будет ноль. Это может помочь кому-то, у кого есть несколько иные требования 🙂

Источник

Функция MySQL для поиска количества рабочих дней между двумя датами

Excel имеет функцию NETWORKDAYS (), которая находит количество рабочих дней между двумя датами.

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

29 ответов

вычисляет количество рабочих дней между датой начала @S и датой окончания @E.

предполагается, что дата окончания (@E) не предшествует дате начала (@S). Совместимость с DATEDIFF в том же дата начала и дата окончания дает ноль рабочих дней. Игнорирует праздники.

затем объедините 49 значений в таблице в строку.

пожалуйста, дайте мне знать, если вы обнаружите какие-либо ошибки.

-редактировать улучшенная таблица:

улучшена строка: ‘0123444401233334012222340111123400001234000123440’

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

вам, конечно, нужно заполнить его всеми датами за любой период времени, с которым вы можете работать в своем приложении. Поскольку в году всего 365 (или 366) дней, переход с 1900 на 2100 не имеет большого значения. Просто убедитесь, что вы загружаете его со всеми датами, а не только праздники.

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

предостережение: я работаю в основном с MS SQL и не работал с MySQL в течение длительного времени, поэтому вам может потребоваться настроить выше. Например, я даже не помню, имеет ли MySQL битный тип данных.

это решение использует в основном тот же подход, что и у Роджера, за исключением того, что метод генерации матрицы намного сложнее. Примечание: этот выход такого решения не совместимые с ЧИСТРАБДНИ.

как и в решении Роджера, это вычисляет количество рабочих дней между датой начала (@S) и датой окончания (@E) без необходимости определения хранимой процедуры. Предполагается, что дата окончания не ранее даты начала. Использование одной и той же даты начала и окончания приведет к 0. Праздники не учитываются.

основное различие между этим и решением Роджера заключается в том, что матрица и результирующая строка цифр построены сложным алгоритмом, который я не включил. Выход этого алгоритма проверяется модульным тестом (см. входы и выходы теста ниже). В матрице пересечение любой заданной пары значений x и y (WEEKDAY(@S) и WEEKDAY(@E)) дает разницу в рабочих днях между двумя значениями. Порядок назначения на самом деле неважно, как эти два сложены вместе, чтобы построить позицию.

рабочие дни понедельник-пятница

49 значений в таблице объединены в следующую строку:

В конце концов, правильное выражение:

Я проверил следующие входы и выходы, используя это решение:

просто для дальнейшего использования. Ничто из вышеперечисленного не работало для меня, но модифицированная версия @Jeff Kooser:

предлагаемый строк быть не так?

DATEDIFF (from, to) исключает ‘to’. Точно так же и эта строка:

Источник

T-SQL получает количество рабочих дней между двумя датами

Я хочу рассчитать количество рабочих дней между двумя заданными датами. Например, если я хочу рассчитать рабочие дни между 2013-01-10 и 2013-01-15, результат должен быть 3 рабочих дня (я не принимаю во внимание последний день в этом интервале, и я вычитаю субботу и воскресенье). У меня есть следующий код, который работает для большинства случаев, кроме одного в моем примере.

Как я могу это сделать? Должен ли я проходить через все дни и проверять их? Или есть простой способ сделать это.

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

Вот пример, который создает таблицу календаря с 30-летними датами (2000 → 2029), но требует только 200 КБ на диске (136 КБ, если вы используете сжатие страницы). Это почти гарантированно меньше, чем выделение памяти, необходимое для обработки некоторого CTE или другого набора во время выполнения.

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

Дополнительная информация о таблицах календаря:

Дополнительная информация о генераторных установках без циклов:

Для таких вещей я склонен поддерживать таблицу календаря, которая также включает в себя праздничные дни и т.д.

script я для этого заключается в следующем (обратите внимание, что я не писал его @, я забыл, где я его нашел)

Как только вы создали таблицу, найти количество рабочих дней легко:

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

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

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

Если у меня есть таблица с праздниками, вы можете добавить это тоже:

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

Я сделал свой код в SQL SERVER 2008 (MS SQL). Он отлично работает для меня. Надеюсь, это поможет вам.

Источник

SQL: решение задачи о рабочем времени

Здравствуйте, в эфире опять Радио SQL! Сегодня у нас решение задачи, которую мы передавали в нашем предыдущем эфире, и обещали разобрать в следующий раз. И вот этот следующий раз наступил.

Задача вызвала живой отклик у гуманоидов галактики Млечный путь (и неудивительно, с их-то трудовым рабством, которое они до сих пор почитают за благо цивилизации). К сожалению, на третьей планете отложили запуск космической обсерватории «Спектр-РГ» в конце июля 2019 года РХ (летоисчисление местное), с помощью которого планировалось транслировать эту передачу. Пришлось искать альтернативные пути передачи, что привело к небольшому опозданию сигнала. Но всё хорошо, что хорошо кончается.

Сразу скажу, что в разборе задачи не будет никакой магии, не надо искать тут откровений или ждать какой-то особо эффективной (или особо какой-нибудь в любом другом смысле) реализации. Это просто разбор задачи. В нём те, кто не знает, как подступаться к решению таких задач, смогут посмотреть, как же их решать. Тем более, что ничего страшного тут нет.

Есть несколько временных интервалов, заданных датой-временем своего начала и конца (пример в синтаксисе PostgreSQL):

Требуется в один SQL-запрос (ц) вычислить продолжительность каждого интервала в рабочих часах. Считаем, что рабочими у нас являются будние дни с понедельника по пятницу, рабочее время всегда с 10:00 до 19:00. Кроме того, в соответствии с производственным календарём РФ существует некоторое количество официальных праздничных дней, которые рабочими не являются, а какие-то из выходных дней, наоборот, являются рабочими из-за переноса тех самых праздников. Укороченность предпраздничных дней учитывать не надо, считаем их полными. Так как праздничные дни год от года меняются, то есть задаются явным перечислением, то ограничимся датами только из 2018 и 2019 годов. Уверен, что при необходимости решение можно будет легко дополнить.

Надо к исходным периодам из periods добавить один столбец с продолжительностью в рабочих часах. Вот какой должен получиться результат:

Исходные данные на корректность не проверяем, считаем всегда start_time

Источник