oracle SQL：如何将工时储存到轮班时间中？

我真的需要一个帮助，以找到正确的SQL逻辑，以获得预期的结果（第三个表）。我必须存储小时，从一个表A链接到一个表B，其中包含存在转移时间。
在我的练习中，将时间存储到早班或中班并不重要，我可以机械地将时间堆叠到轮班时间中，例如按项目编号排序。
表A -有效小时数
| 姓名|项目编号|小时数|
| - ------|- ------|- ------|
| 马丁先生|项目1|1个|
| 马丁先生|项目2|第二章|
| 马丁先生|项目3|1个|
| 史密斯先生|项目1|四个|
表B -存在移位时间
| 姓名|AM开始时间|AM结束时间|PM开始时间|PM结束时间|
| - ------|- ------|- ------|- ------|- ------|
| 马丁先生|九点整|十二点整|14点整|17点整|
| 史密斯先生|十点整|十二点整|13点整|16点整|
我希望得到的结果：
| 姓名|项目编号|开始时间|结束时间|小时数|
| - ------|- ------|- ------|- ------|- ------|
| 马丁先生|项目1|九点整|十点整|1个|
| 马丁先生|项目2|十点整|十二点整|第二章|
| 马丁先生|项目3|14点整|15点整|1个|
| 马丁先生|无项目|15点整|17点整|第二章|
| 史密斯先生|项目1|十点整|十二点整|第二章|
| 史密斯先生|项目1|13点整|15点整|第二章|
| 史密斯先生|无项目|15点整|16点整|1个|
如果你能帮助我，我想提供一杯啤酒;）
非常感谢。

您可以取消透视AM和PM班次，然后计算班次和项目的运行总小时数，并在小时数重叠时将两者合并（在Oracle 11 g上测试，因为10 g非常旧）：

WITH shifts AS (
  SELECT name,
         shift,
         start_time,
         end_time,
         SUM(EXTRACT(HOUR FROM end_time - start_time))
           OVER (PARTITION BY name ORDER BY start_time)
         - EXTRACT(HOUR FROM end_time - start_time) AS total_start_time,
         SUM(EXTRACT(HOUR FROM end_time - start_time))
           OVER (PARTITION BY name ORDER BY start_time) AS total_end_time
  FROM   shift_time
  UNPIVOT (
    (start_time, end_time)
    FOR shift IN (
      (am_start_time, am_end_time) AS 'AM',
      (pm_start_time, pm_end_time) AS 'PM'
    )
  )
),
hours AS (
  SELECT name,
         project_number,
         hours,
         SUM(hours) OVER (PARTITION BY name ORDER BY project_number) - hours
           AS start_hour,
         SUM(hours) OVER (PARTITION BY name ORDER BY project_number)
           AS end_hour
  FROM   effective_hours
)
SELECT s.name,
       h.project_number,
       s.shift,
       s.start_time + NUMTODSINTERVAL(GREATEST(start_hour, total_start_time) - total_start_time, 'HOUR') AS start_time,
       s.start_time + NUMTODSINTERVAL(LEAST(end_hour, total_end_time) - total_start_time, 'HOUR') AS end_time
FROM   shifts s
       INNER JOIN hours h
       ON (   s.name = h.name
          AND s.total_start_time < h.end_hour
          AND s.total_end_time   > h.start_hour );

其中，对于示例数据：

CREATE TABLE effective_hours (name, project_number, hours) AS
SELECT 'ALICE', 'PROJECT 1', 1 FROM DUAL UNION ALL
SELECT 'ALICE', 'PROJECT 2', 2 FROM DUAL UNION ALL
SELECT 'ALICE', 'PROJECT 3', 1 FROM DUAL UNION ALL
SELECT 'BERYL', 'PROJECT 1', 4 FROM DUAL UNION ALL
SELECT 'CAROL', 'PROJECT 1', 7 FROM DUAL;

CREATE TABLE shift_time (name, AM_start_time, AM_end_time, PM_start_time, PM_end_time) AS
SELECT 'ALICE', INTERVAL '9' HOUR,  INTERVAL '12' HOUR, INTERVAL '14' HOUR, INTERVAL '17' HOUR FROM DUAL UNION ALL
SELECT 'BERYL',  INTERVAL '10' HOUR, INTERVAL '12' HOUR, INTERVAL '13' HOUR, INTERVAL '16' HOUR FROM DUAL UNION ALL
SELECT 'CAROL',  INTERVAL '9' HOUR, INTERVAL '12' HOUR, INTERVAL '13' HOUR, INTERVAL '19' HOUR FROM DUAL;

其输出：
| 姓名|项目编号|班次|开始时间|结束时间|
| - ------|- ------|- ------|- ------|- ------|
| 爱丽丝|项目2|上午|+000000000 10：00：00.00000000|+000000000 12：00：00.00000000|
| 爱丽丝|项目1|上午|+000000000 09：00：00.00000000|+000000000 10：00：00.00000000|
| 爱丽丝|项目3|项目经理|+00000000 14时00分00秒|+000000000 15：00：00.00000000|
| 绿柱石|项目1|上午|+000000000 10：00：00.00000000|+000000000 12：00：00.00000000|
| 绿柱石|项目1|项目经理|+00000000 13时00分00秒|+000000000 15：00：00.00000000|
| 卡罗尔|项目1|上午|+000000000 09：00：00.00000000|+000000000 12：00：00.00000000|
| 卡罗尔|项目1|项目经理|+000000000 13：00：00.00000000|+000000000 17：00：00.00000000|
fiddle

oracle SQL：如何将工时储存到轮班时间中？

1条答案

相关问题

热门标签

最新问答