本文介绍了根据Oracle SQL中的营业时间计算营业时间的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我希望根据工作时间来计算任务开始和结束时间之间的时间.我有以下示例数据:
I am looking to calculate hours between a start and end of time of a task based on business hours. I have the following sample data:
TASK | START_TIME | END_TIME A | 16-JAN-17 10:00 | 23-JAN-17 11:35 B | 18-JAN-17 17:53 | 19-JAN-17 08:00 C | 13-JAN-17 13:00 | 17-JAN-17 14:52 D | 21-JAN-17 10:00 | 30-JAN-17 08:52我需要计算出两者之间的区别,但要基于以下工作时间:
and I need to work out the difference between the two but based on the following business hours:
Mon - Sat 08:00 - 18:00我知道如何编写计算,但不确定如何将营业时间添加到计算中.
I know how to write the calculation but not sure what I do to add the business hours into the calculation.
任何建议将不胜感激.
推荐答案使用相关的层次结构查询为每个工作日生成一行,然后对每一天的小时总数求和:
Use a correlated hierarchical query to generate one row for each work day and then sum the hours for each day:
SELECT task, COALESCE( SUM( end_time - start_time ), 0 ) * 24 AS total_hours FROM ( SELECT task, GREATEST( t.start_time, d.column_value + INTERVAL '8' HOUR ) AS start_time, LEAST( t.end_time, d.column_value + INTERVAL '18' HOUR ) AS end_time FROM your_table t LEFT OUTER JOIN TABLE( CAST( MULTISET( SELECT TRUNC( t.start_time + LEVEL - 1 ) FROM DUAL WHERE TRUNC( t.start_time + LEVEL - 1 ) - TRUNC( t.start_time + LEVEL - 1, 'iw' ) < 6 CONNECT BY TRUNC( t.start_time + LEVEL - 1 ) < t.end_time ) AS SYS.ODCIDATELIST ) ) d ON ( t.end_time > d.column_value + INTERVAL '8' HOUR AND t.start_time < d.column_value + INTERVAL '18' HOUR ) ) GROUP BY task;更多推荐
根据Oracle SQL中的营业时间计算营业时间
发布评论