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 Go to next message
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 #674174 is a reply to message #674173] Tue, 08 January 2019 13:09 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
Re: Determining a due date based on business hours [message #674175 is a reply to message #674173] Tue, 08 January 2019 14:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
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 #674185 is a reply to message #674175] Wed, 09 January 2019 10:10 Go to previous messageGo to next message
TerryM
Messages: 3
Registered: January 2019
Junior Member
Many thanks Michael, that is a great starting point for me.
Re: Determining a due date based on business hours [message #674187 is a reply to message #674185] Wed, 09 January 2019 10:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Yes, it does not cover many cases (in addition to special holidays) like case where due time is greater than the time between the create date and midnight or when due time have to be spread over 2 or more days.

[Updated on: Thu, 10 January 2019 01:25]

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 Go to previous messageGo to next message
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 #674214 is a reply to message #674207] Thu, 10 January 2019 15:52 Go to previous messageGo to next message
TerryM
Messages: 3
Registered: January 2019
Junior Member
Many thanks for taking the time to help. I think you are right with the need for a function and supporting tables and I also need to get further clarification from the business on all the rules to apply.
Re: Determining a due date based on business hours [message #674221 is a reply to message #674214] Fri, 11 January 2019 08:08 Go to previous messageGo to next message
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 Go to previous message
Michel Cadot
Messages: 68625
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

Previous Topic: wm_concat to listagg
Next Topic: Insert Statement taking more than 2 hrs for 4 million records
Goto Forum:
  


Current Time: Fri Mar 29 06:12:57 CDT 2024