在ORACLE中只计算日期之间的工作时数

大家好，我写了一个oracle查询，它是根据8小时计算工作时间，但我想根据8.5小时的结果，有一个小的变化，但我没有得到它，请帮助。现在根据开始和结束日期，它应该返回8.5工作时间，但它是返回8小时的工作时间，请协助。

查询

with dates as (
              select  to_date('20-oct-2022 09:00:00','dd-mon-yyyy hh24:mi:ss') start_dt,
                     to_date('20-oct-2022 17:30:00','dd-mon-yyyy hh24:mi:ss') end_dt
             from  dual
              ),
        -- get  work hours for each date
        t as (
              select  case level
                        when 1 then greatest(start_dt,trunc(start_dt) + 8 / 24)
                        else trunc(start_dt) + level - 16 / 24
                      end start_dt,
                      case connect_by_isleaf
                        when 1 then least(end_dt,trunc(end_dt) + 17 / 24)
                        else trunc(start_dt) + level - 7 / 24
                      end end_dt
                from  dates
                connect by level <= trunc(end_dt) - trunc(start_dt) + 1
             )
select  sum(greatest(end_dt - start_dt,0)) * 24 work_hours
  from  t
  where trunc(start_dt) - trunc(start_dt,'iw') < 5

您不需要生成所有日期;您可以直接计算小时数：

SELECT start_dt,
       end_dt,
       ROUND(
         (
           -- Calculate the full weeks difference from the start of ISO weeks.
           ( TRUNC( end_dt, 'IW' ) - TRUNC( start_dt, 'IW' ) ) * 8.5 * (5/7)
           -- Add the full days for the final week.
           + LEAST( TRUNC( end_dt ) - TRUNC( end_dt, 'IW' ), 5 ) * 8.5
           -- Subtract the full days from the days of the week before the start date.
           - LEAST( TRUNC( start_dt ) - TRUNC( start_dt, 'IW' ), 5 ) * 8.5
           -- Add the hours of the final day
           + CASE 
             WHEN end_dt - TRUNC( end_dt, 'IW' ) < 5 -- Weekday
             THEN LEAST(
                    GREATEST(
                      end_dt  - (TRUNC( end_dt ) + INTERVAL '09:00' HOUR TO MINUTE),
                      0
                    ) * 24,
                    8.5
                  )
             ELSE 0
             END
           -- Subtract the hours of the day before the range starts.
           - CASE 
             WHEN start_dt - TRUNC( start_dt, 'IW' ) < 5 -- Weekday
             THEN LEAST(
                    GREATEST(
                      start_dt - (TRUNC( start_dt ) + INTERVAL '09:00' HOUR TO MINUTE),
                      0
                    ) * 24,
                    8.5
                  )
             ELSE 0
             END
         ),
         15 -- Number of decimal places
       ) AS work_hours_diff
FROM   dates;

其中，对于示例数据：

CREATE TABLE dates (start_dt, end_dt) AS
SELECT DATE '2022-10-20' + INTERVAL '09:00:00' HOUR TO SECOND,
       DATE '2022-10-20' + INTERVAL '17:30:00' HOUR TO SECOND
FROM   DUAL
UNION ALL
SELECT DATE '2022-10-20' + INTERVAL '10:00:00' HOUR TO SECOND,
       DATE '2022-10-21' + INTERVAL '17:30:00' HOUR TO SECOND
FROM   DUAL
UNION ALL
SELECT DATE '2022-11-19' + INTERVAL '23:46:00' HOUR TO SECOND,
       DATE '2022-11-21' + INTERVAL '12:06:00' HOUR TO SECOND
FROM   DUAL
UNION ALL
SELECT DATE '2022-11-18' + INTERVAL '17:30:00' HOUR TO SECOND,
       DATE '2022-11-21' + INTERVAL '09:00:00' HOUR TO SECOND
FROM   DUAL
UNION ALL
SELECT DATE '2022-11-19' + INTERVAL '12:26:45' HOUR TO SECOND,
       DATE '2022-11-21' + INTERVAL '11:02:15' HOUR TO SECOND
FROM   DUAL
UNION ALL
SELECT DATE '2022-11-21' + INTERVAL '11:02:15' HOUR TO SECOND,
       DATE '2022-11-19' + INTERVAL '12:26:45' HOUR TO SECOND
FROM   DUAL;

注意：负值有效，因为该行的开始日期晚于结束日期。*

fiddle

在ORACLE中只计算日期之间的工作时数

1条答案

相关问题

热门标签

最新问答