Hi there,
I may be overthinking this query and would really appreciate some input/kick in the pants. How in the heck do I get daily overtime based on a 40 hour work week? If the week isn't complete but an employee has racked up more than 40 hours I need to know how much per day once reached.
Employee John Madden
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
Sun/Sat Weekly Total = 5 hours overtime.
This is what I have so far but can't seem to get anywhere close to DailyOvertime.
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')
)
Surely you meant to add those dates using the MM-DD-YYYY format?
Anyway, all you need to do is subtract 40 from the runningtotal if it's over 40. Otherwise return zero:
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