Home » SQL & PL/SQL » SQL & PL/SQL » Determining a due date based on business hours (Otracle 12.1)
Determining a due date based on business hours [message #674173] |
Tue, 08 January 2019 12:21 |
|
TerryM
Messages: 3 Registered: January 2019
|
Junior Member |
|
|
Hello,
I having trouble writing some code to return a due date based on adding a variable number of hours (up to 100) to a create date considering business hours. Any help with this code would be greatly appreciated, the answer can be sql or pl/sql based.
If I have a due time of say 3 hours then I add it to a create date and get the due date back. The problem is the hours are only counted during business hours of Monday Friday 8:30 AM to 5:30 PM.
If the create date is at 4:30 PM and the due time is 3 hours then I use 1 hour the first day (4:30 to 5:30) then the other 2 hours would go to the next business day. In this case the due date would be 8:30 AM + the 2 remaining hours so the due date is 10:30 AM the next business day. I have to account for weekends and holidays so in the above example if we used the first hour on a Friday the due date would be 10:30 AM the next Monday (assuming both Friday and Monday are non-holidays).
Many thanks
Terry.
|
|
|
|
Re: Determining a due date based on business hours [message #674175 is a reply to message #674173] |
Tue, 08 January 2019 14:35 |
|
Michel Cadot
Messages: 68693 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
SQL> with
2 data as (
3 select to_date('&crdate','DD/MM/YYYY HH24:MI')+&duetime/24 stdduedate from dual
4 ),
5 compute as (
6 select stdduedate, stdduedate+15/24 nextdate, stdduedate+15/24+2 nextweek
7 from data
8 )
9 select case
10 when to_number(to_char(stdduedate,'SSSSS')) <= 63000 then stdduedate
11 when to_char(nextdate,'fmDy','nls_date_language=american') not in ('Sat','Sun') then nextdate
12 else nextweek
13 end duedate
14 from compute
15 /
Enter value for crdate: 08/01/2019 10:00
Enter value for duetime: 3
DUEDATE
-------------------
08/01/2019 13:00:00
1 row selected.
SQL> /
Enter value for crdate: 08/01/2019 16:30
Enter value for duetime: 3
DUEDATE
-------------------
09/01/2019 10:30:00
1 row selected.
SQL> /
Enter value for crdate: 11/01/2019 16:30
Enter value for duetime: 3
DUEDATE
-------------------
14/01/2019 10:30:00
1 row selected.
[Updated on: Tue, 08 January 2019 14:35] Report message to a moderator
|
|
|
|
|
Re: Determining a due date based on business hours [message #674207 is a reply to message #674173] |
Thu, 10 January 2019 09:59 |
|
JPBoileau
Messages: 88 Registered: September 2017
|
Member |
|
|
Open time, work time, break time, lunch time... These can get fairly involved. I suggest writing a function for this, along with supporting tables.
This is only an example. Mine assumes a workweek of M-Thu (8:00am - 5:00pm) and Fri (8:00am - 12:00pm), and South Carolina (my locale) state holidays.
CREATE OR REPLACE FUNCTION DUE_DATE(START_DATE DATE, HOURS_PROMISED NUMBER, MAX_DAYS NUMBER DEFAULT 365) RETURN DATE IS
CURSOR MaxHours IS
SELECT
CASE WHEN MAXDAYS.WORKDATE = H.HOLIDAY_DATE THEN
MAXDAYS.WORKDATE
ELSE
GREATEST(MAXDAYS.WORKDATE + NVL(TO_NUMBER(START_TIME), 0)/2400, START_DATE)
END AS START_DATE_WORK,
CASE WHEN MAXDAYS.WORKDATE = H.HOLIDAY_DATE THEN
MAXDAYS.WORKDATE
ELSE
MAXDAYS.WORKDATE + NVL(TO_NUMBER(END_TIME), 0)/2400
END AS END_DATE_WORK,
CASE WHEN (MAXDAYS.WORKDATE = H.HOLIDAY_DATE) OR (NWW.DAY_OF_WEEK IS NULL) THEN
0
ELSE
( (MAXDAYS.WORKDATE + TO_NUMBER(END_TIME)/2400) - GREATEST(MAXDAYS.WORKDATE + NVL(TO_NUMBER(START_TIME), 0)/2400, START_DATE) ) * 24
END AS DATE_HOURS_WORKED
FROM
(SELECT (TRUNC(START_DATE) + ROWNUM-1) AS WORKDATE, TRIM(TO_CHAR(TRUNC(START_DATE + ROWNUM-1), 'DAY')) AS DOW FROM DUAL CONNECT BY LEVEL <= MAX_DAYS) MAXDAYS,
NORMALWORKWEEK NWW,
HOLIDAYS H
WHERE
MAXDAYS.DOW = NWW.DAY_OF_WEEK(+) AND
H.HOLIDAY_DATE(+) = MAXDAYS.WORKDATE
ORDER BY 1;
NHOURS NUMBER;
DATE_PROMISED DATE := SYSDATE;
BEGIN
NHOURS := HOURS_PROMISED;
FOR MREC IN MAXHOURS
LOOP
-- DBMS_OUTPUT.PUT_LINE(TO_CHAR(MREC.START_DATE_WORK) || ' - ' || TO_CHAR(MREC.END_DATE_WORK) || ' Max Hours worked: ' || to_char(MREC.DATE_HOURS_WORKED));
IF MREC.DATE_HOURS_WORKED < NHOURS THEN
NHOURS := NHOURS - MREC.DATE_HOURS_WORKED;
ELSE
DATE_PROMISED := MREC.START_DATE_WORK + (NHOURS/24);
NHOURS := 0;
END IF;
IF NHOURS <= 0 THEN
EXIT;
END IF;
END LOOP;
RETURN DATE_PROMISED;
END;
/
Examples:
Order came in at midnight this morning and has 20 hours of work needed.
DEV1> SELECT DUE_DATE(TRUNC(SYSDATE), 20) FROM DUAL;
DUE_DATE(TRUNC(SYSD
-------------------
2019-01-14 15:00:00
Order came in at current time (presently 2019-01-10 10:56:48) and has 20 hours of work needed
DEV1> SELECT DUE_DATE(SYSDATE, 20) FROM DUAL;
DUE_DATE(SYSDATE,20
-------------------
2019-01-15 08:56:48
Order came in on 2018-12-31 at midnight and has 16 hours of work needed
DEV1> SELECT DUE_DATE(to_date('2018-12-31'), 16) FROM DUAL;
DUE_DATE(TO_DATE('2
-------------------
2019-01-02 15:00:00
JP
|
|
|
|
Re: Determining a due date based on business hours [message #674221 is a reply to message #674214] |
Fri, 11 January 2019 08:08 |
|
JPBoileau
Messages: 88 Registered: September 2017
|
Member |
|
|
I forgot to put the tables and their data:
CREATE TABLE NORMALWORKWEEK as (
select 'MONDAY' AS DAY_OF_WEEK, '0800' AS START_TIME, '1700' AS END_TIME from dual UNION ALL
select 'TUESDAY' AS DAY_OF_WEEK, '0800' AS START_TIME, '1700' AS END_TIME from dual UNION ALL
select 'WEDNESDAY' AS DAY_OF_WEEK, '0800' AS START_TIME, '1700' AS END_TIME from dual UNION ALL
select 'THURSDAY' AS DAY_OF_WEEK, '0800' AS START_TIME, '1700' AS END_TIME from dual UNION ALL
select 'FRIDAY' AS DAY_OF_WEEK, '0800' AS START_TIME, '1200' AS END_TIME from dual);
CREATE TABLE HOLIDAYS AS (
select to_date('01-JAN-2019', 'DD-MON-YYYY') AS HOLIDAY_DATE from dual UNION ALL
select to_date('21-JAN-2019', 'DD-MON-YYYY') AS HOLIDAY_DATE from dual UNION ALL
select to_date('18-FEB-2019', 'DD-MON-YYYY') AS HOLIDAY_DATE from dual UNION ALL
select to_date('10-MAY-2019', 'DD-MON-YYYY') AS HOLIDAY_DATE from dual UNION ALL
select to_date('27-MAY-2019', 'DD-MON-YYYY') AS HOLIDAY_DATE from dual UNION ALL
select to_date('04-JUL-2019', 'DD-MON-YYYY') AS HOLIDAY_DATE from dual UNION ALL
select to_date('02-SEP-2019', 'DD-MON-YYYY') AS HOLIDAY_DATE from dual UNION ALL
select to_date('11-NOV-2019', 'DD-MON-YYYY') AS HOLIDAY_DATE from dual UNION ALL
select to_date('28-NOV-2019', 'DD-MON-YYYY') AS HOLIDAY_DATE from dual UNION ALL
select to_date('29-NOV-2019', 'DD-MON-YYYY') AS HOLIDAY_DATE from dual UNION ALL
select to_date('24-DEC-2019', 'DD-MON-YYYY') AS HOLIDAY_DATE from dual UNION ALL
select to_date('25-DEC-2019', 'DD-MON-YYYY') AS HOLIDAY_DATE from dual UNION ALL
select to_date('26-DEC-2019', 'DD-MON-YYYY') AS HOLIDAY_DATE from dual);
JP
|
|
|
Re: Determining a due date based on business hours [message #674223 is a reply to message #674221] |
Fri, 11 January 2019 11:26 |
|
Michel Cadot
Messages: 68693 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
You can do it with a recursive query.
Using JP holidays table but still using working day/hour from Monday to Friday and from 8:30 to 17:30 (I let all the rows to see how the query works, to get only the result activate the commented last WHERE clause -- the first examples are the ones I used in my previous post, the last 2 ones to show how it works with holidays and even week-end closes to holidays and for duetime greater a day):
SQL> with
2 compute (duedate, duetime, prev_dttype, lvl) as (
3 select to_date('&crdate','DD/MM/YYYY HH24:MI'), &duetime/24, '', 0 lvl
4 from dual
5 union all
6 select case
7 when prev_dttype in ('HO','WE') then duedate+1
8 -- 30600 = 8:30, 63000 = 17:30
9 when duedate+duetime <= trunc(duedate)+63000/86400 then duedate+duetime
10 else trunc(duedate+1)+30600/86400
11 end,
12 case
13 when prev_dttype in ('HO','WE') then duetime
14 when duedate+duetime <= trunc(duedate)+63000/86400 then 0
15 else (duetime*86400-(63000-to_number(to_char(duedate,'SSSSS'))))/86400
16 end,
17 case
18 when h.HOLIDAY_DATE is not null then 'HO'
19 when to_char(duedate,'fmDy','nls_date_language=american') in ('Sat','Sun')
20 then 'WE'
21 else 'WD'
22 end,
23 c.lvl+1
24 from compute c left outer join HOLIDAYS h on HOLIDAY_DATE = trunc(duedate)
25 where h.HOLIDAY_DATE is not null
26 or to_char(duedate,'fmDy','nls_date_language=american') in ('Sat','Sun')
27 or duetime > 0
28 )
29 select duedate, duetime*24 duetime, prev_dttype
30 from compute
31 -- where lvl = (select max(lvl) from compute)
32 /
Enter value for crdate: 08/01/2019 10:00
Enter value for duetime: 3
DUEDATE DUETIME PR
------------------- ---------- --
08/01/2019 10:00:00 3
08/01/2019 13:00:00 0 WD
2 rows selected.
SQL> /
Enter value for crdate: 08/01/2019 16:30
Enter value for duetime: 3
DUEDATE DUETIME PR
------------------- ---------- --
08/01/2019 16:30:00 3
09/01/2019 08:30:00 2 WD
09/01/2019 10:30:00 0 WD
3 rows selected.
SQL> /
Enter value for crdate: 11/01/2019 16:30
Enter value for duetime: 3
DUEDATE DUETIME PR
------------------- ---------- --
11/01/2019 16:30:00 3
12/01/2019 08:30:00 2 WD
12/01/2019 10:30:00 0 WE
13/01/2019 10:30:00 0 WE
14/01/2019 10:30:00 0 WE
5 rows selected.
SQL> /
Enter value for crdate: 18/01/2019 16:30
Enter value for duetime: 3
DUEDATE DUETIME PR
------------------- ---------- --
18/01/2019 16:30:00 3
19/01/2019 08:30:00 2 WD
19/01/2019 10:30:00 0 WE
20/01/2019 10:30:00 0 WE
21/01/2019 10:30:00 0 WE
22/01/2019 10:30:00 0 HO
6 rows selected.
SQL> /
Enter value for crdate: 17/01/2019 12:30
Enter value for duetime: 36
DUEDATE DUETIME PR
------------------- ---------- --
17/01/2019 12:30:00 36
18/01/2019 08:30:00 31 WD
19/01/2019 08:30:00 22 WD
20/01/2019 08:30:00 13 WE
21/01/2019 08:30:00 13 WE
22/01/2019 08:30:00 13 HO
23/01/2019 08:30:00 13 WD
24/01/2019 08:30:00 4 WD
24/01/2019 12:30:00 0 WD
9 rows selected.
You can also do it, in the same way, with MODEL clause.
And I bet you can do it with the new Pattern Matching method but I have to admit I never used it, maybe Solomon will see this topic and provide such a solution.
[Updated on: Sat, 12 January 2019 04:28] Report message to a moderator
|
|
|
Goto Forum:
Current Time: Fri Sep 27 13:38:44 CDT 2024
|