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

I have the following tables on oracle DB PRB_phases which contains: PRBID,CREATION_DATE,INSERTIONDATE,ENDDATE,PRIORITY,COMPLEXITY,PHASES and table RFQ_HOLD_TIME which contains RFQID,HOLDDATE,UNHOLDDATE where i save the time that the problem is on hold status so on the following query except the information about the problem also calculate the result of holdtime and add it to the "phase"deadline but i want to exclude the weekend when add days for deadline phases an also when add time to deadline from the holdtime.

我在oracle DB PRB_phases上有以下表格，其中包含：PRBID，CREATION_DATE，INSERTIONDATE，ENDDATE，PRIORITY，COMPLEXITY，PHASES和表RFQ_HOLD_TIME，其中包含RFQID，HOLDDATE，UNHOLDDATE，其中我保存问题处于保持状态的时间，因此在以下查询除了有关问题的信息之外还计算保持时间的结果并将其添加到“阶段”截止日期，但我想在截止日期添加天数时排除周末，也可以在保留时间的截止日期添加时间。

the query is the following and it works the only thing that i want help is to exclude the weekends:

查询是以下，它的工作唯一我想要的帮助是排除周末：

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

2 个解决方案

#1

I have not read your entire submission but to remove weekends from a calculation you can use the following:

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

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;

This is taken from a larger query that is used to calculate the working_minutes between 2 date values. The important part for you is the WHERE clause, this subtracts weekends from the calculation, it could probably be amended to fir your needs however the format of you question means it is difficult to establish what you require exactly.

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

#2

Assuming that you need a way to count the days, excluding weekend, in an interval of dates, you may try something like this:

假设您需要一种方法来计算日期间隔周末的日期，您可以尝试这样的事情：

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

This will evaluate the number of days between 01/01/2016 and 31/01/2016, without counting saturdays and sundays

这将评估2016年1月1日至2016年1月31日之间的天数，而不计算星期六和星期日

#1