oracle 计算员工在特定日期范围之间的小时数总和

4smxwvx5 于 2023-03-29 发布在 Oracle

关注(0)|答案(1)|浏览(186)

我想计算每个员工在某个日期范围（START_DATE_ABSENCE & END_DATE_ABSENCE）内缺勤的小时数。困难的是，该员工可能在周六或周日也有工作时间，或者工作时间少于40小时。我不知道如何执行此操作。
以下查询的结果如下：Result of query
工作日是员工的工作计划。每个员工的工作计划都不同。该员工可能在星期六f.e.工作。

SELECT EMP_A.EMPLOYEE_NUMBER, 
        EMP_P.START_DATE AS START_DATE_ABSENCE, 
        EMP_P.END_DATE AS END_DATE_ABSENCE,
        EMP_A.MONDAY,
        EMP_A.TUESDAY,
        EMP_A.WEDNESDAY,
        EMP_A.THURSDAY,
        EMP_A.FRIDAY,
        EMP_A.SATURDAY,
        EMP_A.SUNDAY
FROM XXAS.XXAS_FHT_EMPLOYEES_ALL_MV EMP_A
JOIN XXAS.XXAS_FHT_EMP_PERIODS_R EMP_P
ON EMP_P.EMPLOYEE_NUMBER = EMP_A.EMPLOYEE_NUMBER
WHERE EMP_A.EMPLOYEE_NUMBER = '1000599'
AND EMP_P.START_DATE >= SYSDATE

有人能解决这个问题吗？
我试着计算日期范围内的星期一（和所有其他日子）的数量，但这似乎不是一个可靠的解决方案，我遇到了性能问题：

WITH mondays AS (
  SELECT EMP_P.START_DATE + LEVEL - 1 AS M
  FROM XXAS.XXAS_FHT_EMP_PERIODS_R EMP_P
  CONNECT BY LEVEL <= EMP_P.END_DATE - EMP_P.START_DATE + 1
)
SELECT count(*) NR_OF_MONDAYS
FROM MONDAYS
WHERE (TO_CHAR(M, 'D') = '2')
AND ROWNUM < 5
;

oracle

来源：https://stackoverflow.com/questions/75835513/calculate-the-sum-of-hours-of-an-employee-between-a-specific-date-range

1条答案

按热度按时间

ds97pgxw1#

在Oracle 12中，您可以使用CROSS APPLY和分层查询为缺勤范围中的每个日期生成行，并将其与一周中的日期和预计小时数相关联：

SELECT EMP_A.EMPLOYEE_NUMBER, 
       EMP_P.START_DATE AS START_DATE_ABSENCE, 
       EMP_P.END_DATE AS END_DATE_ABSENCE,
       EMP_A.MONDAY,
       EMP_A.TUESDAY,
       EMP_A.WEDNESDAY,
       EMP_A.THURSDAY,
       EMP_A.FRIDAY,
       EMP_A.SATURDAY,
       EMP_A.SUNDAY,
       total_absence
FROM   XXAS.XXAS_FHT_EMPLOYEES_ALL_MV EMP_A
       JOIN XXAS.XXAS_FHT_EMP_PERIODS_R EMP_P
       ON EMP_P.EMPLOYEE_NUMBER = EMP_A.EMPLOYEE_NUMBER
       CROSS APPLY(
         SELECT SUM(
                  CASE TRUNC(EMP_P.START_DATE + LEVEL - 1)
                       - TRUNC(EMP_P.START_DATE + LEVEL - 1, 'IW')
                  WHEN 0 THEN EMP_A.MONDAY
                  WHEN 1 THEN EMP_A.TUESDAY
                  WHEN 2 THEN EMP_A.WEDNESDAY
                  WHEN 3 THEN EMP_A.THURSDAY
                  WHEN 4 THEN EMP_A.FRIDAY
                  WHEN 5 THEN EMP_A.SATURDAY
                  WHEN 6 THEN EMP_A.SUNDAY
                  END
                ) AS total_absence
         FROM   DUAL
         CONNECT BY EMP_P.START_DATE + LEVEL - 1 <= EMP_P.END_DATE
       )
WHERE  EMP_A.EMPLOYEE_NUMBER = '1000599'
AND    EMP_P.START_DATE >= SYSDATE

其中，对于示例数据：

CREATE TABLE XXAS.XXAS_FHT_EMPLOYEES_ALL_MV (
  employee_number, monday, tuesday, wednesday, thursday, friday, saturday, sunday) AS
SELECT '1000599', 8, 8, 8, 8, 8, 0, 0 FROM DUAL

CREATE TABLE XXAS.XXAS_FHT_EMP_PERIODS_R (
  employee_number, start_date, end_date) AS
SELECT '1000599', DATE '2023-04-26', DATE '2023-05-03' FROM DUAL

输出：
| 员工编号|开始日期缺席|缺勤结束日期|星期一|星期二|星期三|星期四|星期五|星期六|星期日|总计_缺席|
| --------------|--------------|--------------|--------------|--------------|--------------|--------------|--------------|--------------|--------------|--------------|
| 1000599|2019 -04-26 00：00：00|2023年5月3日00时00分|八|八|八|八|八|0|0|四十八|
fiddle

赞(0）回复(0）举报 2023-03-29

我来回答

oracle 计算员工在特定日期范围之间的小时数总和

1条答案

相关问题

热门标签

最新问答