在SQL/ORACLE中，我如何找到比昨天早14个非周末的日期？

Question

在SQL/ORACLE中，我需要输出的日期是昨天日期(today - 1)之前的14个非周末(不包括SAT/SUN)。

例如，如果今天是5月16日，我需要：

4-5-16=昨天

14-APR-16 = 14个非周末返回

下面帮助我找到昨天的日期(不包括周末日期)：

SELECT decode(to_char(sysdate,'dy'),'sun',sysdate-2,'tue',sysdate-3,sysdate-1) from dual

任何帮助都将不胜感激！

Answer 1

如果你真的只想跳过周末，根本不想考虑其他节假日，你可以使用递归CTE，锚分支从昨天开始，递归分支倒计时，而忽略周末，直到你到达足够远的地方。

您可以使用以下内容生成日期：

with r (the_date, days_ago, work_days_ago) as (
  select trunc(sysdate) - 1, 0, 0
  from dual
  union all
  select r.the_date - 1, days_ago + 1,
    work_days_ago + case
      when to_char(r.the_date - 1, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH')
        in ('SAT','SUN') then 0 else 1 end
  from r
  where days_ago < 28
  and work_days_ago < 14
)
select r.*, to_char(r.the_date, 'DY')
from r;

THE_DATE    DAYS_AGO WORK_DAYS_AGO TO_CHAR(R.TH
--------- ---------- ------------- ------------
04-MAY-16      0         0 WED
03-MAY-16      1         1 TUE
02-MAY-16      2         2 MON
01-MAY-16      3         2 SUN
30-APR-16      4         2 SAT
29-APR-16      5         3 FRI
28-APR-16      6         4 THU
27-APR-16      7         5 WED
26-APR-16      8         6 TUE
25-APR-16      9         7 MON
24-APR-16     10         7 SUN
23-APR-16     11         7 SAT
22-APR-16     12         8 FRI
21-APR-16     13         9 THU
20-APR-16     14        10 WED
19-APR-16     15        11 TUE
18-APR-16     16        12 MON
17-APR-16     17        12 SUN
16-APR-16     18        12 SAT
15-APR-16     19        13 FRI
14-APR-16     20        14 THU

为了得到你想要的：

...
select max(the_date)
from r
where work_days_ago = 14;

MAX(THE_D
---------
14-APR-16

在递归分支中，days_ago < 28只是为递归提供一个静态停止条件；它永远不会达到这个条件，但它需要足够高，这样您才能确定这一点。然后，work_days_ago < 14会停止查看超出您实际需要的任何更远的内容。你不能只检查它，因为你一到周末就会得到一个循环，因为这个数字不会递增。

在外部查询中，我使用了max，以防您在星期二运行它；然后递归将得到三天，work_days_ago为14，表示星期一、星期日和星期六；您只需要其中最近的日期。(或者，代替max()，再次明确排除周末)。

Answer 2

如果问题只针对工作日和周末，就不需要复杂的解决方案。像下面这样简单的代码就足够了。

OP没有回应我的澄清请求，所以我需要做一个假设。下面的代码对该要求的解释如下：

从今天开始，回到日历上，倒数工作天数。最近一个工作日的计数为0，下一个工作日为1，我们返回count = 14的工作日。(但是，代码不是通过计数得到答案的；根据所描述的需求，它使用按事例进行简单的算术计算)。

如果需求不同，则可以以相同的方式修改解决方案；无论需求是什么，解决方案都应该同样简单。

select trunc(sysdate) - case to_char(sysdate, 'DY') when 'SAT' then 19
                                                    when 'SUN' then 20
                                                    else            21 end
from ...  (etc.)

(根据Alex在下面的评论中的观察更正-谢谢Alex!)

Answer 3

工作日是一个棘手的问题。这是由于每个国家的假日都不同，有时是在国内。假日可以落在不同的日子，比如复活节，也可以在不同的日子庆祝。

您将需要一个自定义包来计算此值，并需要一个列出每年假日和周末的表。(假设你周末不工作)

您是在计算一个国家的一个地点还是多个地点？

就像这样

CREATE TABLE HOLIDAYS as
( ID NUMBER(9), --primary key
HOLIDAY_ID NUMBER(9),
FOR_YEAR NUMBER(9),  -- a constraint FOR_YEAR > 1 and < 9999
HOLIDAY_NAME VARCHAR2(200),
HOLIDAY_LENGTH NUMBER(9) DEFAULT 1,
LOCATION_ID NUMBER(9),
HOLIDAY_DATE DATE);

--在FOR_YEAR、LOCATION_ID、HOLIDAY_ID上添加一个唯一的键--插入每年的每个假日和周末，以及另一个您开展业务的地点表

在一个包中，你的计算应该是这样的

FUNCTION GET_BUSINESS_DAYS(start_date_in IN DATE, end_date_in DATE)
RETURN NUMBER 
IS
v_days NUMBER(9);
v_holidays NUMBER(9);
BEGIN
--check that start_date_in and end_date_in are valid dates
--check that end_date_in is after start_date_in

--assumes holidays are only one day which is wrong in some cultures!
SELECT start_date_in - end_date_in 
INTO v_days 
FROM DUAL;

SELECT SUM(HOLIDAY_LENGTH) 
INTO v_holidays
FROM HOLIDAYS
WHERE HOLIDAY_DATE BETWEEN start_date_in and end_date_in;

v_days := v_days - v_holidays;
--check that is this a positive value
RETURN v_days;
END GET_BUSINESS_DAYS;

有许多边缘情况需要检查，但这应该会指出正确的方向