Reputation: 137
Add target hours onto the next working day if job is logged after a certain time
Bit of a specific request / query but hope that I can explain it correctly, and that it makes sense.
- The working day is 8am-5pm Monday-Friday
- Each job has a target response time eg 1 hour, 2 hours, 4 hours
- Some jobs it shows the target response time as outside of the working day eg a 4 hour job logged at 4:15pm will show a target response time of 8:15pm.
What I would like to do (and not even sure if it is possible) is:
If the priority_code is GC04 (1 hour job) and the time logged is after 4pm on a Monday-Fri take whatever time is before 5pm and add the remainder on to the next working day from 8am. So an example would be 1 hour job logged at 4:15pm on Monday would show a target response time of 8:15am on Tuesday morning. (45 minutes used on Monday and 15 minutes carried over to Tuesday).
If the priority_code is GC05 (2 hour job) and the time logged is after 3pm on a Monday-Fri take whatever time is before 5pm and add the remainder on to the next working day from 8am. So an example would be 2 hour job logged at 3:15pm on Monday would show a target response time of 8:15am on Tuesday morning. (1 hour 45 minutes used on Monday and 15 minutes carried over to Tuesday).
If the priority_code is GC06 (4 hour job) and the time logged is after 1pm on a Monday-Fri take whatever time is before 5pm and add the remainder on to the next working day from 8am. So an example would be 4 hour job logged at 1:15pm on Monday would show a target response time of 8:15am on Tuesday morning. (3 hours 45 minutes used on Monday and 15 minutes carried over to Tuesday).
THANK YOU TO ALEX POOLE I'VE NOW GOT IT WORKING Coding is below
select job_number, priority_code, job_entry_date, clock_start,
target_comp_date,
case
when to_char(target_time, 'Dy', 'NLS_DATE_LANGUAGE=ENGLISH') = 'Fri'
and floor((target_time - trunc(target_time)) * 24) >= 17
then target_time + 2 + 63/24
when floor((target_time - trunc(target_time)) * 24) >= 17
then target_time + 15/24
else target_time
end as target_time
from (
select job_number, priority_code, job_entry_date, clock_start,
TARGET_COMP_DATE,
CASE
WHEN PRIORITY_CODE IN ('GC01','GC02','GC03','GC04','GC05','GC06','GC07')
THEN
clock_start
+ case priority_code
when 'GC01' then 1
when 'GC02' then 2
when 'GC03' then 0.5
when 'GC04' then 1
when 'GC05' then 2
when 'GC06' then 4
when 'GC07' then 24
end
/ 24
ELSE
TARGET_COMP_DATE END as target_time
from (
select job_number, priority_code, job_entry_date, target_comp_date,
case
when to_char(job_entry_date, 'Dy', 'NLS_DATE_LANGUAGE=ENGLISH') = 'Fri'
and floor((job_entry_date - trunc(job_entry_date)) * 24) >= 17
then trunc(job_entry_date) + 80/24
when to_char(job_entry_date, 'Dy', 'NLS_DATE_LANGUAGE=ENGLISH') = 'Sat'
then trunc(job_entry_date) + 56/24
when to_char(job_entry_date, 'Dy', 'NLS_DATE_LANGUAGE=ENGLISH') =
'Sun'
or floor((job_entry_date - trunc(job_entry_date)) * 24) >= 17
then trunc(job_entry_date) + 32/24
when floor((job_entry_date - trunc(job_entry_date)) * 24) < 8
then trunc(job_entry_date) + 8/24
else job_entry_date
end as clock_start
from job
)
)
Upvotes: 0
Views: 128
Answers (2)
Reputation: 191520
jobs can be logged at any time of the day using online web-forms, not just between 8am-5pm
This means that if a job is logged out-of-hours you need to treat it as if it was actually logged at the start of the next working day. (Note that I'm following your question in treating all Mon-Fri as working days - there is nothing in your question about public holidays for instance. Dealing with those would probably be a separate question.)
If you want to break it down you can first figure out when the clock starts on a given job, based on whether it was logged inside or outside the workday. There are a few ways to do this but since you have to deal with weekends I've chosen to do this as:
select id, priority_code, logged_time,
case
when to_char(logged_time, 'Dy', 'NLS_DATE_LANGUAGE=ENGLISH') = 'Fri'
and floor((logged_time - trunc(logged_time)) * 24) >= 17
then trunc(logged_time) + 56/24
when to_char(logged_time, 'Dy', 'NLS_DATE_LANGUAGE=ENGLISH') = 'Sat'
then trunc(logged_time) + 56/24
when to_char(logged_time, 'Dy', 'NLS_DATE_LANGUAGE=ENGLISH') = 'Sun'
or floor((logged_time - trunc(logged_time)) * 24) >= 17
then trunc(logged_time) + 32/24
when floor((logged_time - trunc(logged_time)) * 24) < 8
then trunc(logged_time) + 8/24
else logged_time
end as clock_start
from your_table;
The floor((logged_time - trunc(logged_time)) * 24) gives you the hour that a job was logged, so you can see if that was lower than 8 (i.e. 8am) or greater than or equal to 17 (i.e. 5pm). Jobs logged at or after 17:00 on Friday or any time at weekends have their clock-start time pushed to the following Monday; jobs logged at or after 17:00 on other days are pushed to the following morning. That's using date arithmetic - 8/24 is 8 hours, 32/24 is 1 day and 8 hours, 56/24 is 2 days and 8 hours etc.
You can then put that into an inline view or CTE to simplify further calculations:
with cte1 (id, priority_code, logged_time, clock_start) as (
...
)
select id, priority_code, logged_time, clock_start,
clock_start
+ case priority_code when 'GC04' then 1 when 'GC05' then 2 when 'GC06' then 4 end
/ 24 as target_time
from cte1;
which will give you the basic target time; and then you can adjust that using similar logic to my earlier answer about timestamps and working-day-only logging, but this time using more date manipulation with fractional days instead of with intervals:
with cte1 (id, priority_code, logged_time, clock_start) as (
select id, priority_code, logged_time,
case
when to_char(logged_time, 'Dy', 'NLS_DATE_LANGUAGE=ENGLISH') = 'Fri'
and floor((logged_time - trunc(logged_time)) * 24) >= 17
then trunc(logged_time) + 80/24
when to_char(logged_time, 'Dy', 'NLS_DATE_LANGUAGE=ENGLISH') = 'Sat'
then trunc(logged_time) + 56/24
when to_char(logged_time, 'Dy', 'NLS_DATE_LANGUAGE=ENGLISH') = 'Sun'
or floor((logged_time - trunc(logged_time)) * 24) >= 17
then trunc(logged_time) + 32/24
when floor((logged_time - trunc(logged_time)) * 24) < 8
then trunc(logged_time) + 8/24
else logged_time
end as clock_start
from your_table
),
cte2 (id, priority_code, logged_time, clock_start, target_time) as (
select id, priority_code, logged_time, clock_start,
clock_start
+ case priority_code when 'GC04' then 1 when 'GC05' then 2 when 'GC06' then 4 end
/ 24 as target_time
from cte1
)
select id, priority_code, logged_time, clock_start,
case
when to_char(target_time, 'Dy', 'NLS_DATE_LANGUAGE=ENGLISH') = 'Fri'
and floor((target_time - trunc(target_time)) * 24) >= 17
then target_time + 63/24
when floor((target_time - trunc(target_time)) * 24) >= 17
then target_time + 15/24
else target_time
end as target_time
from cte2;
which with some made-up data for the scenarios I've thought about gives, with the days added just for info to make things hopefully slightly clearer:
ID PRIO LOGGED_TIME CLOCK_START TARGET_TIME LOGGED_DAY CLOCK_DAY TARGET_DAY
--- ---- ------------------- ------------------- ------------------- ---------- --------- ----------
1 GC06 2019-05-27 12:59:59 2019-05-27 12:59:59 2019-05-27 16:59:59 Mon Mon Mon
2 GC06 2019-05-27 13:15:00 2019-05-27 13:15:00 2019-05-28 08:15:00 Mon Mon Tue
3 GC05 2019-05-27 14:59:59 2019-05-27 14:59:59 2019-05-27 16:59:59 Mon Mon Mon
4 GC05 2019-05-27 15:15:00 2019-05-27 15:15:00 2019-05-28 08:15:00 Mon Mon Tue
5 GC04 2019-05-27 15:59:59 2019-05-27 15:59:59 2019-05-27 16:59:59 Mon Mon Mon
6 GC04 2019-05-27 16:15:00 2019-05-27 16:15:00 2019-05-28 08:15:00 Mon Mon Tue
7 GC04 2019-05-27 16:59:59 2019-05-27 16:59:59 2019-05-28 08:59:59 Mon Mon Tue
8 GC04 2019-05-27 17:00:00 2019-05-28 08:00:00 2019-05-28 09:00:00 Mon Tue Tue
9 GC04 2019-05-28 07:59:59 2019-05-28 08:00:00 2019-05-28 09:00:00 Tue Tue Tue
10 GC04 2019-05-28 08:00:00 2019-05-28 08:00:00 2019-05-28 09:00:00 Tue Tue Tue
11 GC06 2019-05-31 12:59:59 2019-05-31 12:59:59 2019-05-31 16:59:59 Fri Fri Fri
12 GC06 2019-05-31 13:15:00 2019-05-31 13:15:00 2019-06-03 08:15:00 Fri Fri Mon
13 GC05 2019-05-31 14:59:59 2019-05-31 14:59:59 2019-05-31 16:59:59 Fri Fri Fri
14 GC05 2019-05-31 15:15:00 2019-05-31 15:15:00 2019-06-03 08:15:00 Fri Fri Mon
15 GC04 2019-05-31 15:59:59 2019-05-31 15:59:59 2019-05-31 16:59:59 Fri Fri Fri
16 GC04 2019-05-31 16:15:00 2019-05-31 16:15:00 2019-06-03 08:15:00 Fri Fri Mon
17 GC04 2019-05-31 16:59:59 2019-05-31 16:59:59 2019-06-03 08:59:59 Fri Fri Mon
18 GC04 2019-05-31 17:00:00 2019-06-03 08:00:00 2019-06-03 09:00:00 Fri Mon Mon
19 GC04 2019-06-01 12:00:00 2019-06-03 08:00:00 2019-06-03 09:00:00 Sat Mon Mon
20 GC04 2019-06-02 12:00:00 2019-06-03 08:00:00 2019-06-03 09:00:00 Sun Mon Mon
21 GC04 2019-06-03 07:59:59 2019-06-03 08:00:00 2019-06-03 09:00:00 Mon Mon Mon
22 GC04 2019-06-03 08:00:00 2019-06-03 08:00:00 2019-06-03 09:00:00 Mon Mon Mon
Note that the CTE construct is providing aliases for the column expression from the with (...) clause, and that those do not have table aliases - as those aliases are unrelated to the tables inside the CTE. So it's with cte1 (id, priority_code, ... and not with cte1 (your_table.id, your_table.priority_code, ....
Also note that the semicolon at the end is a statement separator, required or optional (or configurable) in some clients, but invalid in others - it can cause ORA-00933 or ORA-00911 errors, and possibly others, in dynamic SQL, JDBC etc.; so ODBC probably also doesn't expect to see that final semicolon character.
If ODBC (or your version) doesn't allow CTEs - suggested by the 'not a SELECT statement' error you mentioned in a comment - then you can use inline views instead:
select id, priority_code, logged_time, clock_start,
case
when to_char(target_time, 'Dy', 'NLS_DATE_LANGUAGE=ENGLISH') = 'Fri'
and floor((target_time - trunc(target_time)) * 24) >= 17
then target_time + 2 + 63/24
when floor((target_time - trunc(target_time)) * 24) >= 17
then target_time + 15/24
else target_time
end as target_time
from (
select id, priority_code, logged_time, clock_start,
clock_start
+ case priority_code when 'GC04' then 1 when 'GC05' then 2 when 'GC06' then 4 end
/ 24 as target_time
from (
select id, priority_code, logged_time,
case
when to_char(logged_time, 'Dy', 'NLS_DATE_LANGUAGE=ENGLISH') = 'Fri'
and floor((logged_time - trunc(logged_time)) * 24) >= 17
then trunc(logged_time) + 80/24
when to_char(logged_time, 'Dy', 'NLS_DATE_LANGUAGE=ENGLISH') = 'Sat'
then trunc(logged_time) + 56/24
when to_char(logged_time, 'Dy', 'NLS_DATE_LANGUAGE=ENGLISH') = 'Sun'
or floor((logged_time - trunc(logged_time)) * 24) >= 17
then trunc(logged_time) + 32/24
when floor((logged_time - trunc(logged_time)) * 24) < 8
then trunc(logged_time) + 8/24
else logged_time
end as clock_start
from your_table
)
);
Added to db<>fiddle.
It's a little unclear but to keep a pre-calculated target_comp_date for other priorities you could change the first inline view (based on cte2) to have nested case expressions:
...
from (
select id, priority_code, logged_time, clock_start,
case when priority_code in ('GC04', 'GC05', 'GC06') then
-- for these, calculate the target time based on clock-start as before
clock_start
+ case priority_code when 'GC04' then 1 when 'GC05' then 2 when 'GC06' then 4 end
/ 24
else
-- for any other priority use the original pre-calculated time
target_comp_date
end as target_time
from (
select id, priority_code, logged_time, target_comp_date,
...
The innermost inline view needs to include that extra column in its select list, so it's visible to that nested case expression.
The system will show the target_comp_date incorrect as 12am
You should probably be fixing that existing code then, rather than trying to fudge the result it gives you.
Upvotes: 0
Reputation: 191520
A slightly convoluted approach, which assumes your logged_time column is a timestamp (easy to adapt if it's a date), and that it can't be out-of-hours:
select id, priority_code, logged_time,
logged_time
+
-- response time
(
interval '1' hour
* case priority_code when 'GC04' then 1 when 'GC05' then 2 when 'GC06' then 4 end
)
+
-- actual time adjustment
(
-- possible time adjustment...
(
-- gap between 17:00 and 08:00
interval '15' hour
+
-- weekend days, only if Friday
(
interval '2' day
* case when to_char(logged_time, 'Dy', 'NLS_DATE_LANGUAGE=ENGLISH') = 'Fri'
then 1 else 0 end
)
)
*
-- ... but only if target exceeds 17:00
case when extract
(
hour from logged_time
+
-- response time
(
interval '1' hour
* case priority_code when 'GC04' then 1 when 'GC05' then 2 when 'GC06' then 4 end
)
) > 16 then 1 else 0 end
)
as target_time
from your_table;
which with some sample data like yours and just before your cut-offs, both on a Friday and Monday, gives:
ID PRIO LOGGED_TIME TARGET_TIME
---------- ---- --------------------- ---------------------
1 GC06 2019-05-26 12:59:59.0 2019-05-26 16:59:59.0
2 GC06 2019-05-26 13:15:00.0 2019-05-27 08:15:00.0
3 GC05 2019-05-26 14:59:59.0 2019-05-26 16:59:59.0
4 GC05 2019-05-26 15:15:00.0 2019-05-27 08:15:00.0
5 GC04 2019-05-26 15:59:59.0 2019-05-26 16:59:59.0
6 GC04 2019-05-26 16:15:00.0 2019-05-27 08:15:00.0
7 GC06 2019-05-31 12:59:59.0 2019-05-31 16:59:59.0
8 GC06 2019-05-31 13:15:00.0 2019-06-03 08:15:00.0
9 GC05 2019-05-31 14:59:59.0 2019-05-31 16:59:59.0
10 GC05 2019-05-31 15:15:00.0 2019-06-03 08:15:00.0
11 GC04 2019-05-31 15:59:59.0 2019-05-31 16:59:59.0
12 GC04 2019-05-31 16:15:00.0 2019-06-03 08:15:00.0
You can reduce some of the duplication with a CTE or inline view:
select id, priority_code, logged_time,
raw_target_time
+
-- actual time adjustment
(
-- possible time adjustment...
(
-- gap between 17:00 and 08:00
interval '15' hour
+
-- weekend days, only if Friday
(
interval '2' day
* case when to_char(logged_time, 'Dy', 'NLS_DATE_LANGUAGE=ENGLISH') = 'Fri'
then 1 else 0 end
)
)
*
-- ... but only if target exceeds 17:00
case when extract (hour from raw_target_time) > 16 then 1 else 0 end
)
as target_time
from (
select id, priority_code, logged_time,
logged_time
+
-- response time
(
interval '1' hour
* case priority_code when 'GC04' then 1 when 'GC05' then 2 when 'GC06' then 4 end
)
as raw_target_time
from your_table
);
and of course it doesn't need to be laid out like that, I was just trying to make the logic a bit clearer.
Upvotes: 2
Related Questions
- Calculate only working hours between dates in ORACLE
- Adding hours after midnight today
- How can you add working hours to date time
- Alter an oracle job to run every 5 seconds from a specific time till a specific time everyday
- calculate hours based on business hours in Oracle SQL
- Count Jobs Submitted After Every Hour
- Oracle SQL - Shows all jobs logged after 5pm
- Subtracting previous row to current row of timestamp datatype
- Calculate time difference ignorning non-work hours
- Start Date Calculated Given End Date and Working Hours PL/SQL