Вопрос:
У меня есть 2 таблицы: первая содержит дату начала и дату окончания заказа на поставку,
и вторая таблица содержит годовые дни года
-приказ покупки
-Holidays
Я пытаюсь рассчитать количество рабочих дней между двумя датами без выходных и праздничных дней.
вывод должен выглядеть следующим образом:
Start Date | End Date | Business Days
Не могли бы вы помочь мне?
Ответ №1
Вы можете удалить выходные дни без выходных с таким запросом:
select (t.end_date — t.start_date) — count(c.date) from table1 t left join calendar c on c.date between t1.start_date and t1.end_date and to_char(c.date, ‘D’) not in (‘1’, ‘7’) group by t.end_date, t.start_date;
Удаление выходных дней – это больше осложнений. Полные недели имеют два выходных дня, так что это легко. Итак, хорошее приближение:
select (t.end_date — t.start_date) — (count(c.date) + 2 * floor((t.end_date — t.start_date) / 7)) from table1 t left join calendar c on c.date between t1.start_date and t1.end_date and to_char(c.date, ‘D’) not in (‘1’, ‘7’) group by t.end_date, t.start_date;
Это не получает день недели, что существенно, если дата окончания до даты начала, а затем на следующей неделе. Однако эта логика довольно усложняется тем, как Oracle обрабатывает день недели, поэтому, возможно, вышеприведенное приближение достаточно.
Ответ №2
EDIT: я проигнорировал присутствие тега Oracle и перешел в сценарий для SQL Server. Однако концепция не меняется.
Чтобы быть суперточным, я бы создал таблицу с указанным ниже форматом.
Год int, month int, DaysInMonth int, firstOccuranceOfSunday int
Создайте процедуру для извлечения выходных дней из определенного года и месяца в этой таблице.
CREATE FUNCTION [dbo].[GetWeekendsForMonthYear] ( @year int, @month int ) RETURNS @weekends TABLE ( [Weekend] date ) AS BEGIN declare @firstsunday int = 0 Declare @DaysInMonth int = 0 Select @DaysInMonth = DaysInMonth, @firstsunday = FirstSunday from Months Where [Year] = @year and [month] = @month Declare @FirstSaterday int = @firstsunday — 1 declare @CurrentDay int = 0 Declare @CurrentDayIsSunday bit = 0 if @FirstSaterday !< 1 Begin insert into @Weekends values(DATEADD(year, @year -1900, DATEADD(month, @month -1, DATEADD(day, @Firstsaterday -1, 0)))) insert into @Weekends values(DATEADD(year, @year -1900, DATEADD(month, @month -1, DATEADD(day, @FirstSunday -1, 0)))) set @CurrentDayIsSunday = 1 set @CurrentDay = @firstsunday END else begin insert into @Weekends values(DATEADD(year, @year -1900, DATEADD(month, @month -1, DATEADD(day, @FirstSunday -1, 0)))) set @FirstSaterday = @firstsunday + 6 insert into @Weekends values(DATEADD(year, @year -1900, DATEADD(month, @month -1, DATEADD(day, @Firstsaterday -1, 0)))) set @CurrentDayIsSunday = 0 set @CurrentDay = @FirstSaterday end declare @done bit = 0 while @done = 0 Begin if @CurrentDay <= @DaysInMonth Begin If @CurrentDayIsSunday = 1 begin set @CurrentDay = @CurrentDay + 6 set @CurrentDayIsSunday = 0 if @CurrentDay <= @DaysInMonth begin insert into @Weekends Values(DATEADD(year, @year -1900, DATEADD(month, @month -1, DATEADD(day, @CurrentDay -1, 0)))) end end else begin set @CurrentDay = @CurrentDay + 1 set @CurrentDayIsSunday = 1 if @CurrentDay <= @DaysInMonth begin insert into @Weekends Values(DATEADD(year, @year -1900, DATEADD(month, @month -1, DATEADD(day, @CurrentDay -1, 0)))) end end end ELSE begin Set @done = 1 end end RETURN END
При вызове и предоставлении с год и месяц это вернет список дат, представляющих выходные дни.
Теперь, используя эту функцию, создайте процедуру для вызова этой функции один раз, чтобы каждая применимая строка в определенную дату заходила и возвращала значения в соблазнительной.
Заметьте, я публикую это сейчас, чтобы вы могли видеть, что происходит, но я продолжаю работать над кодом. Я буду публиковать обновления по мере их появления.
Далее: Получить список выходных дней (отформатирован) для конкретного daterange, удалить любые даты из этого списка, которые можно найти в таблице праздников.
К сожалению, я должен завтра работать, и я спал.
Ответ №3
Этот запрос должен давать точное количество рабочих дней для каждого диапазона в таблице purchase:
with days as ( select rn, sd + level — 1 dt, sd, ed from (select row_number() over (order by start_date) rn, start_date sd, end_date ed from purchase_order) connect by prior rn = rn and sd + level — 1 <= ed and prior dbms_random.value is not null) select sd start_date, ed end_date, count(1) business_days from days d left join holidays h on holiday_date = d.dt where dt — trunc(dt, ‘iw’) not in (5, 6) and h.holiday_date is null group by rn, sd, ed
Для каждой строки в purchase_orders запрос генерирует даты из этого диапазона (это выполняется подзапросом dates).
Основной запрос проверяет, является ли это днем выходного дня или праздничным днем, и подсчитывает остальную дату.
Иерархический запрос, используемый для создания дат, может привести к замедлению, если в purchase_orders имеется большое количество данных,
или периоды длинные. В этом случае предпочтительным способом является создание таблицы календаря, как уже было предложено в комментариях.
Ответ №4
Поскольку у вас уже есть таблица праздников, вы можете подсчитать праздники между датой начала и окончания и вычесть из разницы между днями между окончанием и датой начала. В выходные дни вам понадобится таблица, содержащая выходные дни, похожие на таблицу праздничных дней, или вы можете создать их, как показано ниже.
with sample_data(id, start_date, end_date) as ( select 1, date ‘2015-03-06’, date ‘2015-03-7’ from dual union all select 2, date ‘2015-03-07’, date ‘2015-03-8’ from dual union all select 3, date ‘2015-03-08’, date ‘2015-03-9’ from dual union all select 4, date ‘2015-02-07’, date ‘2015-06-26’ from dual union all select 5, date ‘2015-04-17’, date ‘2015-08-16’ from dual ) , holidays(holiday) as ( select date ‘2015-01-01’ from dual union all — New Years select date ‘2015-01-19’ from dual union all — MLK Day select date ‘2015-02-16’ from dual union all — Presidents Day select date ‘2015-05-25’ from dual union all — Memorial Day select date ‘2015-04-03’ from dual union all — Independence Day (Observed) select date ‘2015-09-07’ from dual union all — Labor Day select date ‘2015-11-11’ from dual union all — Veterans Day select date ‘2015-11-26’ from dual union all — Thanks Giving select date ‘2015-11-27’ from dual union all — Black Friday select date ‘2015-12-25′ from dual — Christmas ) — If your calendar table doesn’t already hold weekends you can generate — the weekends with these next two subfactored queries (common table Expressions) , firstweekend(weekend, end_date) as ( select next_day(min(start_date),’saturday’), max(end_date) from sample_data union all select next_day(min(start_date),’sunday’), max(end_date) from sample_data ) , weekends(weekend, last_end_date) as ( select weekend, end_date from firstweekend union all select weekend + 7, last_end_date from weekends where weekend+7 <= last_end_date ) — if not already in the same table combine distinct weekend an holiday days — to prevent double counting (in case a holiday is also a weekend). , days_off(day_off) as ( select weekend from weekends union select holiday from holidays ) select id , start_date , end_date , end_date — start_date + 1 — (select count(*) from days_off where day_off between start_date and end_date) business_days from sample_data; ID START_DATE END_DATE BUSINESS_DAYS ———- ———— ———— ————- 1 06-MAR-2015 07-MAR-2015 1 2 07-MAR-2015 08-MAR-2015 0 3 08-MAR-2015 09-MAR-2015 1 4 07-FEB-2015 26-JUN-2015 98 5 17-APR-2015 16-AUG-2015 85