Oracle 计算每周每天加班的分析函数

你好，

我可能会想得太多这个查询，真的很感激你的一些输入/踢裤子。根据每周40小时的工作时间，我每天怎么加班？如果一周还没有完成，但一名员工已经累积了40多个小时，我需要知道每天达到多少。

员工约翰·马登

EmpID      ADate       DailyHours  RunningTotal  DailyOvertime   
  22       1/08/2018     10            10            0
  22       1/09/2018     10            20            0
  22       1/10/2018     12            32            0
  22       1/11/2018     10            42            2
  22       1/12/2018      3            45            3
复制

周日/周六每周总计 = 5小时加班。

这是我到目前为止所拥有的，但似乎无法接近每日加班。

select DISTINCT
EmpID,
ADate,
trunc(ADate, 'Day') as WeekStart,
sum(ClockedHours) Over(Partition by ADate Order by Emp_Id) as DailyHours,
SUM(ClockedHours) OVER(PARTITION BY trunc(ADate, 'Day') ORDER BY Emp_Id, ADate RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as RunningTotal
FROM EmpTimecardDetail
WHERE Emp_Id in (22)
Order by Emp_Id, ADate;

create table EmpTimecard(  
  EmpId        number(4,0),  
  Ename        varchar2(10),  
  ClockedHours number(4,0),
  ADate        date
)

insert into EmpTimecard
values(  
 22, 'John', 10,  
 to_date('1-8-2018','dd-mm-yyyy')
)

insert into EmpTimecard
values(  
 22, 'John', 10,  
 to_date('1-9-2018','dd-mm-yyyy')
)

insert into EmpTimecard
values(  
 22, 'John', 12,  
 to_date('1-10-2018','dd-mm-yyyy')
)

insert into EmpTimecard 
values(  
 22, 'John', 10,  
 to_date('1-11-2018','dd-mm-yyyy')
)

insert into EmpTimecard
values(  
 22, 'John', 3,  
 to_date('1-12-2018','dd-mm-yyyy')
)
复制

您当然打算使用MM-DD-YYYY格式添加这些日期吗？

无论如何，如果运行总数超过40，您需要做的就是从运行总数中减去40。否则返回零:

create table EmpTimecard(  
  EmpId        number(4,0),  
  Ename        varchar2(10),  
  ClockedHours number(4,0),
  ADate        date
);

insert into EmpTimecard
values(  
 22, 'John', 10, to_date('1-8-2018','mm-dd-yyyy')
);

insert into EmpTimecard
values(  
 22, 'John', 10, to_date('1-9-2018','mm-dd-yyyy')
);

insert into EmpTimecard
values(  
 22, 'John', 12, to_date('1-10-2018','mm-dd-yyyy')
);

insert into EmpTimecard 
values(  
 22, 'John', 10, to_date('1-11-2018','mm-dd-yyyy')
);

insert into EmpTimecard
values(  
 22, 'John', 3,  to_date('1-12-2018','mm-dd-yyyy')
);

with rws as (
  select EmpID,
  ADate,
  trunc(ADate, 'Day') as WeekStart,
  sum(ClockedHours) Over(Partition by ADate, EmpId) as DailyHours,
  SUM(ClockedHours) OVER(PARTITION BY trunc(ADate, 'Day') ORDER BY EmpId, ADate RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as RunningTotal
  FROM EmpTimecard
  WHERE EmpId in (22)
)
  select r.*,
         case 
           when runningtotal > 40 then
             runningtotal - 40
           else
             0
         end dailyovertime
  from   rws r
  Order by EmpId, ADate;

EMPID   ADATE                  WEEKSTART              DAILYHOURS   RUNNINGTOTAL   DAILYOVERTIME   
     22 08-JAN-2018 00:00:00   08-JAN-2018 00:00:00             10             10               0 
     22 09-JAN-2018 00:00:00   08-JAN-2018 00:00:00             10             20               0 
     22 10-JAN-2018 00:00:00   08-JAN-2018 00:00:00             12             32               0 
     22 11-JAN-2018 00:00:00   08-JAN-2018 00:00:00             10             42               2 
     22 12-JAN-2018 00:00:00   08-JAN-2018 00:00:00              3             45               5
复制