sql - 计算工作日，包括在oracle SQL中没有日历表的日期之间的假期

Question

好的，所以我已经阅读了很多关于在 sql 中模拟 excel 的 networkdays 功能的可能性的阅读，并得出结论，到目前为止最简单的解决方案是拥有一个日历表，该表将标记工作日或非工作日工作日。但是，由于我无法控制的情况，我们无法获得这样的奢侈品，而且在不久的将来我们不太可能会这样做。

目前，我已经设法将 SQL 中无疑是有效的可怕的低效查询拼凑起来 - 问题是，它一次只能用于单个客户端记录。

SELECT O_ASSESSMENTS.ASM_ID,
       O_ASSESSMENTS.ASM_START_DATE,
       O_ASSESSMENTS.ASM_END_DATE,
      sum(CASE 
              When TO_CHAR(O_ASSESSMENTS.ASM_START_DATE + rownum -1,'Day')
                   = 'Sunday   ' THEN 0
              When TO_CHAR(O_ASSESSMENTS.ASM_START_DATE + rownum -1,'Day')
                   = 'Saturday ' THEN 0
              WHEN O_ASSESSMENTS.ASM_START_DATE + rownum - 1
                   IN ('03-01-2000','21-04-2000','24-04-2000','01-05-2000','29-05-2000','28-08-2000','25-12-2000','26-12-2000','01-01-2001','13-04-2001','16-04-2001','07-05-2001','28-05-2001','27-08-2001','25-12-2001','26-12-2001','01-01-2002','29-03-2002','01-04-2002','06-04-2002','03-06-2002','04-06-2002','26-08-2002','25-12-2002','26-12-2002','01-01-2003','18-04-2003','21-04-2003','05-05-2003','26-05-2003','25-08-2003','25-12-2003','26-12-2003','01-01-2004','09-04-2004','12-04-2004','03-05-2004','31-05-2004','30-08-2004','25-12-2004','26-12-2004','27-12-2004','28-12-2004','01-01-2005','03-01-2005','25-03-2005','28-03-2005','02-05-2005','30-05-2005','29-08-2005','27-12-2005','28-12-2005','02-01-2006','14-04-2006','17-04-2006','01-05-2006','29-05-2006','28-08-2006','25-12-2006','26-12-2006','02-01-2007','06-04-2007','09-04-2007','07-05-2007','28-05-2007','27-08-2007','25-12-2007','26-12-2007','01-01-2008','21-03-2008','24-03-2008','05-05-2008','26-05-2008','25-08-2008','25-12-2008','26-12-2008','01-01-2009','10-04-2009','13-04-2009','04-05-2009','25-05-2009','31-08-2009','25-12-2009','28-12-2009','01-01-2010','02-04-2010','05-04-2010','03-05-2010','31-05-2010','30-08-2010','24-12-2010','27-12-2010','28-12-2010','31-12-2010','03-01-2011','22-04-2011','25-04-2011','29-04-2011','02-05-2011','30-05-2011','29-08-2011','26-12-2011','27-12-2011') 
                   THEN 0 
              ELSE 1 
          END)-1 AS Week_Day
From O_ASSESSMENTS,
     ALL_OBJECTS
WHERE O_ASSESSMENTS.ASM_QSA_ID  IN ('TYPE1')
  AND O_ASSESSMENTS.ASM_END_DATE >= '01/01/2012'
  AND O_ASSESSMENTS.ASM_ID = 'A00000'
  AND ROWNUM <= O_ASSESSMENTS.ASM_END_DATE-O_ASSESSMENTS.ASM_START_DATE+1
GROUP BY
      O_ASSESSMENTS.ASM_ID,
      O_ASSESSMENTS.ASM_START_DATE,
      O_ASSESSMENTS.ASM_END_DATE

基本上，我想知道是否 a) 我应该停止在此上浪费时间或 b) 是否可以让它为多个客户工作？任何指针表示赞赏谢谢!

编辑:进一步说明 - 我已经使用 excel 计算出时间表，但如果我们可以在报告中完成它会是理想的，因为我们希望最终用户能够在没有任何进一步操作的情况下运行相关报告。

编辑:

MarkBannister 的回答虽然缓慢但效果很好(尽管我已经预料到它不是首选的解决方案) - 现在的挑战在于我将其整合到现有报告中!

with
calendar_cte as (select
to_date('01-01-2000')+level-1 calendar_date,
case when to_char(to_date('01-01-2000')+level-1, 'day') in ('sunday   ','saturday ') then 0 when to_date('01-01-2000')+level-1 in ('03-01-2000','21-04-2000','24-04-2000','01-05-2000','29-05-2000','28-08-2000','25-12-2000','26-12-2000','01-01-2001','13-04-2001','16-04-2001','07-05-2001','28-05-2001','27-08-2001','25-12-2001','26-12-2001','01-01-2002','29-03-2002','01-04-2002','06-04-2002','03-06-2002','04-06-2002','26-08-2002','25-12-2002','26-12-2002','01-01-2003','18-04-2003','21-04-2003','05-05-2003','26-05-2003','25-08-2003','25-12-2003','26-12-2003','01-01-2004','09-04-2004','12-04-2004','03-05-2004','31-05-2004','30-08-2004','25-12-2004','26-12-2004','27-12-2004','28-12-2004','01-01-2005','03-01-2005','25-03-2005','28-03-2005','02-05-2005','30-05-2005','29-08-2005','27-12-2005','28-12-2005','02-01-2006','14-04-2006','17-04-2006','01-05-2006','29-05-2006','28-08-2006','25-12-2006','26-12-2006','02-01-2007','06-04-2007','09-04-2007','07-05-2007','28-05-2007','27-08-2007','25-12-2007','26-12-2007','01-01-2008','21-03-2008','24-03-2008','05-05-2008','26-05-2008','25-08-2008','25-12-2008','26-12-2008','01-01-2009','10-04-2009','13-04-2009','04-05-2009','25-05-2009','31-08-2009','25-12-2009','28-12-2009','01-01-2010','02-04-2010','05-04-2010','03-05-2010','31-05-2010','30-08-2010','24-12-2010','27-12-2010','28-12-2010','31-12-2010','03-01-2011','22-04-2011','25-04-2011','29-04-2011','02-05-2011','30-05-2011','29-08-2011','26-12-2011','27-12-2011','01-01-2012','02-01-2012') then 0 else 1 end working_day
from dual
connect by level <= 1825 + sysdate - to_date('01-01-2000') ) 
SELECT 
a.ASM_ID,
a.ASM_START_DATE,
a.ASM_END_DATE,
sum(c.working_day)-1 AS Week_Day 
From 
O_ASSESSMENTS a 
join calendar_cte c
on c.calendar_date between a.ASM_START_DATE and a.ASM_END_DATE 
WHERE a.ASM_QSA_ID  IN ('TYPE1')
and a.ASM_END_DATE >= '01/01/2012'
GROUP BY      
a.ASM_ID,
a.ASM_START_DATE,
a.ASM_END_DATE 

Answer 1

有几种方法可以做到这一点。也许最简单的方法可能是基于 Oracle 的 connect by 创建一个生成虚拟日历表的 CTE。语法，然后将其加入 Assesments 表，如下所示:

with calendar_cte as (
select to_date('01-01-2000')+level-1 calendar_date,
       case when to_char(to_date('01-01-2000')+level-1, 'Day') 
                in ('Sunday   ','Saturday ') then 0
            when to_date('01-01-2000')+level-1
                in ('03-01-2000','21-04-2000','24-04-2000','01-05-2000','29-05-2000','28-08-2000','25-12-2000','26-12-2000','01-01-2001','13-04-2001','16-04-2001','07-05-2001','28-05-2001','27-08-2001','25-12-2001','26-12-2001','01-01-2002','29-03-2002','01-04-2002','06-04-2002','03-06-2002','04-06-2002','26-08-2002','25-12-2002','26-12-2002','01-01-2003','18-04-2003','21-04-2003','05-05-2003','26-05-2003','25-08-2003','25-12-2003','26-12-2003','01-01-2004','09-04-2004','12-04-2004','03-05-2004','31-05-2004','30-08-2004','25-12-2004','26-12-2004','27-12-2004','28-12-2004','01-01-2005','03-01-2005','25-03-2005','28-03-2005','02-05-2005','30-05-2005','29-08-2005','27-12-2005','28-12-2005','02-01-2006','14-04-2006','17-04-2006','01-05-2006','29-05-2006','28-08-2006','25-12-2006','26-12-2006','02-01-2007','06-04-2007','09-04-2007','07-05-2007','28-05-2007','27-08-2007','25-12-2007','26-12-2007','01-01-2008','21-03-2008','24-03-2008','05-05-2008','26-05-2008','25-08-2008','25-12-2008','26-12-2008','01-01-2009','10-04-2009','13-04-2009','04-05-2009','25-05-2009','31-08-2009','25-12-2009','28-12-2009','01-01-2010','02-04-2010','05-04-2010','03-05-2010','31-05-2010','30-08-2010','24-12-2010','27-12-2010','28-12-2010','31-12-2010','03-01-2011','22-04-2011','25-04-2011','29-04-2011','02-05-2011','30-05-2011','29-08-2011','26-12-2011','27-12-2011') 
                then 0 
            else 1
       end working_day
from dual
connect by level <= 36525 + sysdate - to_date('01-01-2000') )
SELECT a.ASM_ID,
       a.ASM_START_DATE,
       a.ASM_END_DATE,
       sum(c.working_day) AS Week_Day
From O_ASSESSMENTS a
join calendar_cte c 
  on c.calendar_date between a.ASM_START_DATE and a.ASM_END_DATE
WHERE a.ASM_QSA_ID  IN ('TYPE1') and 
      a.ASM_END_DATE >= '01/01/2012' -- and a.ASM_ID = 'A00000'
GROUP BY
      a.ASM_ID,
      a.ASM_START_DATE,
      a.ASM_END_DATE

这将生成一个虚拟表，其中填充了从 2000 年 1 月 1 日到当前日期之后 10 年的日期，所有周末都标记为非工作日，并且所有日期都在第二个 in 中指定。条款(即截至 2011 年 12 月 27 日)也标记为非工作日。

此方法(或将假期日期硬编码到查询中的任何方法)的缺点是，每次定义新的假期日期时，使用此方法的每个查询都必须添加这些日期。