时间戳

首页 > 解决方案 > 查找两个日期之间的第五个工作日sql

sql - 查找两个日期之间的第五个工作日sql

问题描述

我试图找到两个日期之间的第五个工作日，不包括假期。

这是我写的查询：

with s_date as (select TO_DATE('21-04-2020','DD-MM-YYYY') d from dual),
     e_date as (select TO_DATE('01-05-2020','DD-MM-YYYY') d from dual),
     no_of_days as (select abs(trunc(s_date.d -  e_date.d))+1 no from s_date,e_date),
    cal as (select d+rownum-1 dt
            from s_date
            connect by level <= (select no from no_of_days) )
select cal.dt  from cal where to_char(cal.dt, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') NOT IN ('SAT', 'SUN')
and cal.dt not in (select day from T_PUBLIC_HOLIDAYS) order by cal.dt desc;

输出：

DT
1:30-04-2020
2:29-04-2020
3:28-04-2020
4:27-04-2020
5:24-04-2020
6:23-04-2020
7:22-04-2020
8:21-04-2020

如何将输出限制为仅：DT 1：24-04-2020

标签： sqloracledate

解决方案

这将在一个语句中计算多个开始日期和偏移日期的值：

甲骨文设置：

CREATE TABLE test_data ( start_date, days ) AS
SELECT DATE '2019-01-01' + date_offset, days
FROM   ( SELECT LEVEL - 1 AS date_offset FROM DUAL CONNECT BY LEVEL <= 7 )
       CROSS JOIN
       ( SELECT LEVEL - 1 AS days FROM DUAL CONNECT BY LEVEL <= 7 );

CREATE TABLE holidays ( holiday_date ) AS
SELECT DATE '2019-01-01' FROM DUAL UNION ALL
SELECT DATE '2019-01-09' FROM DUAL UNION ALL
SELECT DATE '2019-01-10' FROM DUAL;

创建一个辅助函数来添加工作日。这不是必需的，因为您可以将函数展开到查询中，但它使正在发生的事情变得更加明显：

CREATE FUNCTION add_weekdays(
  start_date IN DATE,
  weekdays   IN NUMBER
) RETURN DATE DETERMINISTIC
IS
BEGIN
  RETURN start_date
         -- add full weeks
         + FLOOR( weekdays / 5 ) * 7
         -- check to see if the remaining days go over a weekend, if so add 2 extra days
         + CASE WHEN ( start_date - TRUNC( start_date, 'IW' ) ) + MOD( weekdays, 5 ) >= 5
           THEN MOD( weekdays, 5 ) + 2
           ELSE MOD( weekdays, 5 )
           END;
END;
/

查询：

WITH offsets ( start_date, days, offset_date, holiday_days ) AS (
  SELECT start_date,
         days,
         add_weekdays( start_date, days ),
         ( SELECT COUNT(*)
           FROM   holidays
           WHERE  holiday_date BETWEEN start_date
                               AND add_weekdays( start_date, days )
         )
  FROM   test_data
UNION ALL
  -- recurse and add the previously found holiday days and look to see if any more holidays overlap
  SELECT start_date,
         days,
         add_weekdays( offset_date, holiday_days ),
         ( SELECT COUNT(*)
           FROM   holidays
           WHERE  holiday_date BETWEEN offset_date + 1
                               AND add_weekdays( offset_date, holiday_days )
         )
  FROM   offsets
  -- stop recursing when no holiday days are found
  WHERE  holiday_days > 0
)
CYCLE start_date, days, offset_date SET is_cycle TO 1 DEFAULT 0
SELECT TO_CHAR( start_date, 'DY YYYY-MM-DD' ) AS start_dt,
       days,
       TO_CHAR( offset_date, 'DY YYYY-MM-DD' ) AS offset_dt,
       ( offset_date - start_date ) - days AS skipped_days
FROM   offsets
WHERE  holiday_days = 0
ORDER BY start_date, days;

输出：

（考试节假日为2019-01-01，跳过天数包括节假日和周末2019-01-09）2019-01-10

START_DT | 天 | OFFSET_DT | SKIPPED_DAYS
:------------- | ---: | :------------- | ------------:
周二 2019-01-01 | 0 | 周三 2019-01-02 | 1
周二 2019-01-01 | 1 | 星期四 2019-01-03 | 1
周二 2019-01-01 | 2 | 周五 2019-01-04 | 1
周二 2019-01-01 | 3 | 星期一 2019-01-07 | 3
周二 2019-01-01 | 4 | 周二 2019-01-08 | 3
周二 2019-01-01 | 5 | 周五 2019-01-11 | 5
周二 2019-01-01 | 6 | 星期一 2019-01-14 | 7
周三 2019-01-02 | 0 | 周三 2019-01-02 | 0
周三 2019-01-02 | 1 | 星期四 2019-01-03 | 0
周三 2019-01-02 | 2 | 周五 2019-01-04 | 0
周三 2019-01-02 | 3 | 星期一 2019-01-07 | 2
周三 2019-01-02 | 4 | 周二 2019-01-08 | 2
周三 2019-01-02 | 5 | 周五 2019-01-11 | 4
周三 2019-01-02 | 6 | 星期一 2019-01-14 | 6
星期四 2019-01-03 | 0 | 星期四 2019-01-03 | 0
星期四 2019-01-03 | 1 | 周五 2019-01-04 | 0
星期四 2019-01-03 | 2 | 星期一 2019-01-07 | 2
星期四 2019-01-03 | 3 | 周二 2019-01-08 | 2
星期四 2019-01-03 | 4 | 周五 2019-01-11 | 4
星期四 2019-01-03 | 5 | 星期一 2019-01-14 | 6
星期四 2019-01-03 | 6 | 周二 2019-01-15 | 6
周五 2019-01-04 | 0 | 周五 2019-01-04 | 0
周五 2019-01-04 | 1 | 星期一 2019-01-07 | 2
周五 2019-01-04 | 2 | 周二 2019-01-08 | 2
周五 2019-01-04 | 3 | 周五 2019-01-11 | 4
周五 2019-01-04 | 4 | 星期一 2019-01-14 | 6
周五 2019-01-04 | 5 | 周二 2019-01-15 | 6
周五 2019-01-04 | 6 | 周三 2019-01-16 | 6
SAT 2019-01-05 | 0 | 星期一 2019-01-07 | 2
SAT 2019-01-05 | 1 | 周二 2019-01-08 | 2
SAT 2019-01-05 | 2 | 周五 2019-01-11 | 4
SAT 2019-01-05 | 3 | 星期一 2019-01-14 | 6
SAT 2019-01-05 | 4 | 周二 2019-01-15 | 6
SAT 2019-01-05 | 5 | 周三 2019-01-16 | 6
SAT 2019-01-05 | 6 | 周四 2019-01-17 | 6
孙 2019-01-06 | 0 | 周二 2019-01-08 | 2
孙 2019-01-06 | 1 | 周五 2019-01-11 | 4
孙 2019-01-06 | 2 | 星期一 2019-01-14 | 6
孙 2019-01-06 | 3 | 周二 2019-01-15 | 6
孙 2019-01-06 | 4 | 周三 2019-01-16 | 6
孙 2019-01-06 | 5 | 周四 2019-01-17 | 6
孙 2019-01-06 | 6 | 周五 2019-01-18 | 6
星期一 2019-01-07 | 0 | 星期一 2019-01-07 | 0
星期一 2019-01-07 | 1 | 周二 2019-01-08 | 0
星期一 2019-01-07 | 2 | 周五 2019-01-11 | 2
星期一 2019-01-07 | 3 | 星期一 2019-01-14 | 4
星期一 2019-01-07 | 4 | 周二 2019-01-15 | 4
星期一 2019-01-07 | 5 | 周三 2019-01-16 | 4
星期一 2019-01-07 | 6 | 周四 2019-01-17 | 4

db<>在这里摆弄

推荐阅读