时间戳

首页 > 解决方案 > 计算两个日期之间的工作日 Oracle

sql - 计算两个日期之间的工作日 Oracle

问题描述

我正在尝试编写一个查询，该查询可以计算收到付款和正在处理之间的工作日数，我开始在 2017 年 12 月收到付款；

select unique trunc(date_received), 
   (case when trunc(date_received) in ('25-DEC-17','26-DEC-17') Then 0 when 
to_char(date_received,'D') <6 Then 1 else 0 end) Working_day
from payments
where date_received between '01-DEC-17' and '31-dec-17'
order by trunc(date_received)

但老实说，我不知道如何进一步处理并添加 date_processed 并计算 date_processed 和 date_received 之间的工作日数......任何帮助将不胜感激......

标签： sqloracle

解决方案

也许不是最理想的，但它工作得很好，而且很容易合并更复杂的检查，比如假期。此查询首先生成两个日期之间的所有日期，然后让您过滤掉所有“不计算在内”的日期。

在这个实现中，我只过滤掉了周末的日子，但是添加假期等检查是很容易的。

with 
  -- YourQuery: I used a stub, but you can use your actual query here, which 
  -- returns a from date and to date. If you have multiple rows, you can also
  -- output some id here, which can be used for grouping in the last step.
  YourQuery as
  ( 
    select 
      trunc(sysdate - 7) as FromDate,
      trunc(sysdate) as ToDate
    from dual),

  -- DaysBetween. This returns all the dates from the start date up to and
  -- including the end date.
  DaysBetween as
  (  
    select
      FromDate,
      FromDate + level - 1 as DayBetween,
      ToDate
    from
      YourQuery
    connect by
      FromDate + level - 1 <= ToDate)

-- As a last step, you can filter out all the days you want. 
-- This default query only filters out Saturdays and Sundays, but you
-- could add a 'not exists' check that checks against a table with known 
-- holidays.
select
  count(*)
from
  DaysBetween d
where
  trim(to_char(DAYINBETWEEN, 'DAY', 'NLS_DATE_LANGUAGE=AMERICAN'))
    not in ('SATURDAY', 'SUNDAY');

推荐阅读