如何在sql中添加天数时排除周末

Question

我在oracle DB PRB_phases上有以下表格，其中包含: PRBID，CREATION_DATE，INSERTIONDATE，ENDDATE，优先级，复杂性，阶段和表RFQ_HOLD_TIME，其中包含RFQID，HOLDDATE，UNHOLDDATE，其中我保存了问题处于搁置状态的时间，所以在以下查询中，除了关于问题的信息外，还计算了搁置时间的结果，并将其添加到“阶段”截止日期，但我想排除周末时，添加天数的截止日期阶段，也当添加时间到截止日期从持有时间。

查询如下，它起作用的唯一一件事是我想要的帮助是排除周末：

select  PRBID,CREATION_DATE,CURRENT_PHASES,NEXT_PHASES,CLASSIFICATION_DEADLINE,FEASIBILITY_DEADLINE,
CASE          
            WHEN HUGE.CURRENT_PHASES = 'Classification'  and to_date(HUGE.CLASSIFICATION_DEADLINE,'DD/MM/RRRR hh12:mi') <= trunc(SYSDATE)  THEN 'DELAYED'
            WHEN HUGE.CURRENT_PHASES = 'Feasibility'  and to_date(HUGE.FEASIBILITY_DEADLINE,'DD/MM/RRRR hh12:mi') <= trunc(SYSDATE) THEN 'DELAYED'
            WHEN HUGE.CURRENT_PHASES = 'Classification'   and to_date(HUGE.CLASSIFICATION_DEADLINE,'DD/MM/RRRR hh12:mi') >= trunc(SYSDATE) THEN 'OK'
            WHEN HUGE.CURRENT_PHASES = 'Feasibility'  and to_date(HUGE.FEASIBILITY_DEADLINE,'DD/MM/RRRR hh12:mi') >= trunc(SYSDATE) THEN 'OK'                        
END
STATE
 from 
(select
 b.PRBID,b.CREATIONDATE CREATION_DATE,b.PHASES CURRENT_PHASES,
 CASE
             WHEN phases = 'Classification' and INSERTIONDATE is not null THEN 'Feasibility'
             WHEN phases = 'Feasibility' and CreationDate is not null THEN 'Completed'
END
NEXT_PHASES,
CASE
            WHEN b.phases = 'Classification' and b.INSERTIONDATE is not null and b.Priority = 1 and b.Complexity = 'minor' and a.holddate is not null and a.unholddate is not null THEN to_char(b.creationdate + 5 + floor(((a.unholddate-a.holddate)*24*60*60)/3600)/24,'dd/mm/rrrr hh12:mi')
            WHEN b.phases = 'Classification' and b.INSERTIONDATE is not null and b.Priority = 1 and b.Complexity = 'minor' and a.holddate is not null and a.unholddate is null THEN 'HOLD'
           ELSE to_char(creationdate+5,'dd/mm/rrrr hh12:mi')
END
CLASSIFICATION_DEADLINE,
CASE
            WHEN b.phases = 'Feasibility' and b.INSERTIONDATE is not null and b.Priority = 1 and b.Complexity = 'minor' and a.holddate is not null and a.unholddate is not null THEN to_char(b.creationdate + 5 + floor(((a.unholddate-a.holddate)*24*60*60)/3600)/24,'dd/mm/rrrr hh12:mi')
            WHEN b.phases = 'Feasibility' and b.INSERTIONDATE is not null and b.Priority = 1 and b.Complexity = 'minor' and a.holddate is not null and a.unholddate is null THEN 'HOLD'
            ELSE to_char(creationdate+10,'dd/mm/rrrr hh12:mi')
END
FEASIBILITY_DEADLINE
from PRB_PHASES b, PRB_HOLD_TIME a
where a.PRBid = b.PRBid
union
select
b.PRBID,b.CREATIONDATE CREATION_DATE,b.PHASES CURRENT_PHASES,
 CASE
             WHEN phases = 'Classification' and INSERTIONDATE is not null THEN 'Feasibility'
             WHEN phases = 'Feasibility' and CreationDate is not null THEN 'Completed'
END
NEXT_PHASES,
CASE
            WHEN b.phases = 'Classification' and b.INSERTIONDATE is not null and b.Priority = 1 and b.Complexity = 'minor' and a.holddate is not null and a.unholddate is not null THEN to_char(b.creationdate + 5 + floor(((a.unholddate-a.holddate)*24*60*60)/3600)/24,'dd/mm/rrrr hh12:mi')
            WHEN b.phases = 'Classification' and b.INSERTIONDATE is not null and b.Priority = 1 and b.Complexity = 'minor' and a.holddate is not null and a.unholddate is null THEN 'HOLD'
           ELSE to_char(creationdate+5,'dd/mm/rrrr hh12:mi')
END
CLASSIFICATION_DEADLINE,
CASE
            WHEN b.phases = 'Feasibility' and b.INSERTIONDATE is not null and b.Priority = 1 and b.Complexity = 'minor' and a.holddate is not null and a.unholddate is not null THEN to_char(b.creationdate + 5 + floor(((a.unholddate-a.holddate)*24*60*60)/3600)/24,'dd/mm/rrrr hh12:mi')
            WHEN b.phases = 'Feasibility' and b.INSERTIONDATE is not null and b.Priority = 1 and b.Complexity = 'minor' and a.holddate is not null and a.unholddate is null THEN 'HOLD'
                    ELSE to_char(creationdate+10,'dd/mm/rrrr hh12:mi')
END
FEASIBILITY_DEADLINE
from PRB_PHASES b,PRB_HOLD_TIME a
where a.PRBid <> b.PRBid)  HUGE

Answer 1

我还没有阅读您的整个提交，但要从计算中删除周末，您可以使用以下方法：

select  sum(end_dt - start_dt) * 24 * 60 work_minutes
      into  v_return
      from  t
      where trunc(start_dt) - trunc(start_dt,'iw') < 5; -- exclude weekends
RETURN v_return;

这取自一个更大的查询，该查询用于计算2个日期值之间的working_minutes。对你来说最重要的部分是WHERE子句，这从计算中减去了周末，它可能会被修改以满足你的需要，但是你的问题的格式意味着很难准确地确定你需要什么。

Answer 2

假设您需要一种方法来计算日期间隔内的天数(不包括周末)，您可以尝试如下所示：

SELECT SUM( DECODE( TO_CHAR(TO_DATE('01-01-2016', 'dd-mm-yyyy') + LEVEL - 1, 'd'), 
                  '6', 0,
                  '7', 0, 
                       1
                  )
           )
      FROM DUAL
CONNECT BY TO_DATE('01-01-2016', 'dd-mm-yyyy') + LEVEL <= TO_DATE('31-01-2016', 'dd-mm-yyyy')
  ORDER BY LEVEL

这将评估2016年1月1日到2016年1月31日之间的天数，不包括周六和周日