计算用户加班费

Question

我需要你的帮助。查询以获取员工完成的超时详细信息。例如:如果用户在下午5.00.01pm后工作，则时间将被视为超时，并且可以超过时间直到11.59.59PM和5.00.01到11.59.59PM只能被视为周一至周五的超时时间。对于周六和周日来说，一整天都可以被认为是超期的.所以我写了一些to_char错误的查询。所以你能帮我找到进一步的解决办法吗？查询:-

SELECT user_id,
  start_time,
  end_time,
  CASE
    when to_char(Clock_in_date, 'DY', 'nls_date_language=english') in ('MON', 'TUE', 'WED', 'THU', 'FRI')
      and to_char(end_date_time, 'hh24:mi:ss')> '17:00:00'
    then
      to_char(cast(end_date_time as timestamp) - cast(trunc(end_date_time) + interval '17' hour as timestamp))
    when to_char(Clock_in_date, 'DY', 'nls_date_language=english') in ('SAT', 'SUN')
    then
      to_char((cast(end_date_time as timestamp) - cast(Clock_in_date as timestamp)))
    else
      'no overtime'
    end as overtime
FROM   employee;

最后的查询应该类似于：

​

​

Answer 1

在你的问题中有一些不明确的东西，比如当开始日期是星期六，结束日期是星期一时，如何对待第六行。如果有人工作了那么长时间(在大多数国家是违法的)，那不全是加班吗？如果不是，那么正常工作时间是多少(9到5？)。

不管怎么说，这是一种方法-一个描述性的方法。首先是样本数据：

WITH
    tbl AS
        (
          Select 1 "ID",    To_Date('29-AUG-22 15:30:00', 'dd-MON-yy hh24:mi:ss') "START_TIME", To_Date('29-AUG-22 17:30:00', 'dd-MON-yy hh24:mi:ss') "END_TIME" From Dual Union All
          Select 2 "ID",    To_Date('30-AUG-22 15:30:00', 'dd-MON-yy hh24:mi:ss') "START_TIME", To_Date('30-AUG-22 20:30:00', 'dd-MON-yy hh24:mi:ss') "END_TIME" From Dual Union All
          Select 3 "ID",    To_Date('31-AUG-22 15:30:00', 'dd-MON-yy hh24:mi:ss') "START_TIME", To_Date('31-AUG-22 17:00:00', 'dd-MON-yy hh24:mi:ss') "END_TIME" From Dual Union All
          Select 4 "ID",    To_Date('01-SEP-22 17:45:00', 'dd-MON-yy hh24:mi:ss') "START_TIME", To_Date('01-SEP-22 23:45:10', 'dd-MON-yy hh24:mi:ss') "END_TIME" From Dual Union All
          Select 5 "ID",    To_Date('02-SEP-22 15:45:00', 'dd-MON-yy hh24:mi:ss') "START_TIME", To_Date('02-SEP-22 23:59:00', 'dd-MON-yy hh24:mi:ss') "END_TIME" From Dual Union All
          Select 6 "ID",    To_Date('27-AUG-22 10:30:00', 'dd-MON-yy hh24:mi:ss') "START_TIME", To_Date('29-AUG-22 17:30:00', 'dd-MON-yy hh24:mi:ss') "END_TIME" From Dual Union All
          Select 7 "ID",    To_Date('28-AUG-22 11:30:00', 'dd-MON-yy hh24:mi:ss') "START_TIME", To_Date('28-AUG-22 20:30:00', 'dd-MON-yy hh24:mi:ss') "END_TIME" From Dual 
        ),

..。为了以不同的方式显示数据，还有另一个名为网格的CTE .

    grid AS
        (
            Select
                ID "ID",
                To_Char(START_TIME, 'dd-MON-yy') "START_DATE",
                To_Char(START_TIME, 'DY') "START_DAY",
                To_Char(START_TIME, 'hh24:mi:ss') "START_TIME",
                CASE WHEN To_Char(START_TIME, 'DY') IN('SAT', 'SUN') THEN 'Weekend' ELSE 'Workday' END "START_TYPE",
                --
                To_Char(END_TIME, 'dd-MON-yy') "END_DATE",
                To_Char(END_TIME, 'DY') "END_DAY",
                To_Char(END_TIME, 'hh24:mi:ss') "END_TIME",
                CASE WHEN To_Char(END_TIME, 'DY') IN('SAT', 'SUN') THEN 'Weekend' ELSE 'Workday' END "END_TYPE",
                --
                CASE 
                    WHEN TRUNC(START_TIME, 'dd') = TRUNC(END_TIME, 'dd') THEN
                        CASE 
                            WHEN To_Char(START_TIME, 'DY') IN('SAT', 'SUN') THEN To_Char(START_TIME, 'hh24:mi:ss') || ' - ' || To_Char(END_TIME, 'hh24:mi:ss')
                        ELSE
                           CASE 
                              WHEN To_Char(START_TIME, 'hh24:mi:ss') > '17:00:00' And To_Char(END_TIME, 'hh24:mi:ss') > To_Char(START_TIME, 'hh24:mi:ss') THEN To_Char(START_TIME, 'hh24:mi:ss') || ' - ' || To_Char(END_TIME, 'hh24:mi:ss')  
                              WHEN To_Char(START_TIME, 'hh24:mi:ss') <= '17:00:00' And To_Char(END_TIME, 'hh24:mi:ss') >= '17:00:00' THEN '17:00:00 - ' || To_Char(END_TIME, 'hh24:mi:ss')
                           END
                        END
                ELSE
                    CASE 
                        WHEN TRUNC(END_TIME, 'dd') - TRUNC(START_TIME, 'dd') = 1 THEN 
                            CASE WHEN To_Char(START_TIME, 'DY') IN('SAT', 'SUN') THEN To_Char(START_TIME, 'hh24:mi:ss') || ' - ' ELSE '17:00:00 - ' END || REPLACE(To_Char(TRUNC(END_TIME, 'dd') - 1, 'hh24:mi:ss'), '00:00:00', '23:59:59')
                        WHEN TRUNC(END_TIME, 'dd') - TRUNC(START_TIME, 'dd') = 2 THEN
                            CASE WHEN To_Char(START_TIME, 'DY') IN('SAT', 'SUN') THEN To_Char(START_TIME, 'hh24:mi:ss') || ' - ' ELSE '17:00:00 - ' END || REPLACE(To_Char(TRUNC(END_TIME, 'dd') - 2, 'hh24:mi:ss'), '00:00:00', '23:59:59')
                    ELSE
                        To_Char(START_TIME, 'hh24:mi:ss') || ' - ' || To_Char(END_TIME, 'hh24:mi:ss')
                    END
                END "OVERTIME_SPAN_0",
                CASE 
                    WHEN TRUNC(END_TIME, 'dd') - TRUNC(START_TIME, 'dd') = 1 THEN 
                        CASE WHEN To_Char(START_TIME, 'DY') IN('SAT', 'SUN') THEN To_Char(TRUNC(START_TIME, 'dd') + 1, 'hh24:mi:ss') || ' - ' ELSE '17:00:00 - ' END || REPLACE(To_Char(END_TIME, 'hh24:mi:ss'), '00:00:00', '23:59:59')
                    WHEN TRUNC(END_TIME, 'dd') - TRUNC(START_TIME, 'dd') = 2 THEN
                        CASE WHEN To_Char(START_TIME, 'DY') IN('SAT', 'SUN') THEN To_Char(TRUNC(START_TIME, 'dd') + 1, 'hh24:mi:ss') || ' - ' ELSE '17:00:00 - ' END || REPLACE(To_Char(TRUNC(END_TIME, 'dd') - 1, 'hh24:mi:ss'), '00:00:00', '23:59:59')
                ELSE
                    Null
                END  "OVERTIME_SPAN_1",
                CASE 
                    WHEN TRUNC(END_TIME, 'dd') - TRUNC(START_TIME, 'dd') = 2 THEN
                        CASE WHEN To_Char(START_TIME, 'DY') IN('SAT', 'SUN') THEN To_Char(TRUNC(START_TIME, 'dd') + 2, 'hh24:mi:ss') || ' - ' ELSE '17:00:00 - ' END || To_Char(END_TIME, 'hh24:mi:ss')
                ELSE
                    Null
                END  "OVERTIME_SPAN_2"
            From
                tbl
        )

..。网格生成的数据集如下所示：

/*
   ID START_DATE START_DAY START_TIME START_TYPE END_DATE  END_DAY END_TIME END_TYPE OVERTIME_SPAN_0       OVERTIME_SPAN_1         OVERTIME_SPAN_2   
----- ---------- --------- ---------- ---------- --------- ------- -------- -------- --------------------- ----------------------- -------------------
    1 29-AUG-22  MON       15:30:00   Workday    29-AUG-22 MON     17:30:00 Workday  17:00:00 - 17:30:00                                               
    2 30-AUG-22  TUE       15:30:00   Workday    30-AUG-22 TUE     20:30:00 Workday  17:00:00 - 20:30:00                                               
    3 31-AUG-22  WED       15:30:00   Workday    31-AUG-22 WED     17:00:00 Workday  17:00:00 - 17:00:00                                               
    4 01-SEP-22  THU       17:45:00   Workday    01-SEP-22 THU     23:45:10 Workday  17:45:00 - 23:45:10                                               
    5 02-SEP-22  FRI       15:45:00   Workday    02-SEP-22 FRI     23:59:00 Workday  17:00:00 - 23:59:00                                               
    6 27-AUG-22  SAT       10:30:00   Weekend    29-AUG-22 MON     17:30:00 Workday  10:30:00 - 23:59:59    00:00:00 - 23:59:59    00:00:00 - 17:30:00 
    7 28-AUG-22  SUN       11:30:00   Weekend    28-AUG-22 SUN     20:30:00 Weekend  11:30:00 - 20:30:00                                               
*/

有一些从样本数据中派生出来的数据可以用来测试并向您展示如何以一种适合于您问题中的逻辑的方式来拆分这些数据。...now，我们有所有的时间跨度，我们需要计算超时值。以下是主要查询：

SELECT
    grid.ID "ID",
    START_DATE,
    START_TIME,
    END_DATE,
    END_TIME,
    OVERTIME_SPAN_0,
    CASE 
        WHEN OVERTIME_SPAN_0 Is Null THEN 0 
    ELSE 
        (   (To_Number(SubStr(OVERTIME_SPAN_0, 12, 2)) * 3600) + (To_Number(SubStr(OVERTIME_SPAN_0, 15, 2)) * 60) + To_Number(SubStr(OVERTIME_SPAN_0, 18, 2))  ) - 
        (   (To_Number(SubStr(OVERTIME_SPAN_0, 1, 2)) * 3600) + (To_Number(SubStr(OVERTIME_SPAN_0, 4, 2)) * 60) + To_Number(SubStr(OVERTIME_SPAN_0, 7, 2))  )
    END "OVERTIME_0",
    OVERTIME_SPAN_1,
    CASE 
        WHEN OVERTIME_SPAN_1 Is Null THEN 0 
    ELSE 
        (   (To_Number(SubStr(OVERTIME_SPAN_1, 12, 2)) * 3600) + (To_Number(SubStr(OVERTIME_SPAN_1, 15, 2)) * 60) + To_Number(SubStr(OVERTIME_SPAN_1, 18, 2))  ) - 
        (   (To_Number(SubStr(OVERTIME_SPAN_1, 1, 2)) * 3600) + (To_Number(SubStr(OVERTIME_SPAN_1, 4, 2)) * 60) + To_Number(SubStr(OVERTIME_SPAN_1, 7, 2))  )
    END "OVERTIME_1",
    OVERTIME_SPAN_2, 
    CASE 
        WHEN OVERTIME_SPAN_1 Is Null THEN 0 
    ELSE 
        (   (To_Number(SubStr(OVERTIME_SPAN_2, 12, 2)) * 3600) + (To_Number(SubStr(OVERTIME_SPAN_2, 15, 2)) * 60) + To_Number(SubStr(OVERTIME_SPAN_2, 18, 2))  ) - 
        (   (To_Number(SubStr(OVERTIME_SPAN_2, 1, 2)) * 3600) + (To_Number(SubStr(OVERTIME_SPAN_2, 4, 2)) * 60) + To_Number(SubStr(OVERTIME_SPAN_2, 7, 2))  )
    END "OVERTIME_2"
FROM
    grid
/*  R e s u l t :
    ID START_DATE START_TIME END_DATE  END_TIME OVERTIME_SPAN_0      OVERTIME_0 OVERTIME_SPAN_1       OVERTIME_1 OVERTIME_SPAN_2     OVERTIME_2
------ ---------- ---------- --------- -------- -------------------- ---------- --------------------- ---------- ------------------- ----------
     1 29-AUG-22  15:30:00   29-AUG-22 17:30:00 17:00:00 - 17:30:00        1800                                0                              0 
     2 30-AUG-22  15:30:00   30-AUG-22 20:30:00 17:00:00 - 20:30:00       12600                                0                              0 
     3 31-AUG-22  15:30:00   31-AUG-22 17:00:00 17:00:00 - 17:00:00           0                                0                              0 
     4 01-SEP-22  17:45:00   01-SEP-22 23:45:10 17:45:00 - 23:45:10       21610                                0                              0 
     5 02-SEP-22  15:45:00   02-SEP-22 23:59:00 17:00:00 - 23:59:00       25140                                0                              0 
     6 27-AUG-22  10:30:00   29-AUG-22 17:30:00 10:30:00 - 23:59:59       48599 00:00:00 - 23:59:59        86399 00:00:00 - 17:30:00      63000 
     7 28-AUG-22  11:30:00   28-AUG-22 20:30:00 11:30:00 - 20:30:00       32400                                0                              0
*/

..。生成的数据集包含从示例数据计算的超时间(以秒为单位)。如前所述，关于加班还有一些问题，但我希望这能帮助你找到自己的解决办法。

问候..。