Due Date Calculated Given Start Date and Working/Lunch Hours PL/SQL

Question

I need some PL/SQL help. I am trying to calculate a due date for a given task, given that the max time is 8 business hours and lunch from 12pm-1pm is not included in this time. For example, If a task comes in at 8am, it is due 5pm. If a task comes in at 11am, it is due 11am next day. I thought this would be fairly easy but I am new to PL/SQL and have just started. My thought process was to use sysdate but that is not going to work. Please help and apologies for not posting code, but I have not got anything that would help.

Answer 1

Messing around with this, the following should be a good starting point for you, pls tweak as you see fit.

It takes a starting date and number of work hours, and returns the due date. Note that it rounds to the nearest hour (so if start date/time is today at 3:45pm, then it uses 4pm as the actual start date/time):

create or replace function get_due_date(i_start_date in date default sysdate, i_hours in number default 8)
return date
as
    l_dte date;
    l_hours number;
begin

    if (i_hours < 1 OR i_hours > 24) then
        raise_application_error(-20001, 'Hours must be between 1 and 24');
    end if;

    l_hours := i_hours + 1;

    select dte 
    into l_dte
    from 
    (
        select dte, to_char(dte, 'HH24') hr, levl, row_number() over(order by levl) rnum
        from (
            -- rounding to nearest hour
            select round(i_start_date, 'HH24') + ((level-1)/24) as dte, level levl
            from dual
            connect by level <= 72
        )
        where to_char(dte, 'HH24') between '08' and '17'
        -- skip lunch hour
        and NOT(to_char(dte, 'HH24') = '12')
    ) x
    where rnum = l_hours;

    return l_dte;

end;

For example:

select sysdate, get_due_date(sysdate, 8) from dual;

Output:

4/10/2015 3:45:21 PM    4/11/2015 3:00:00 PM

Answer 2

You can use the Oracle SCHEDULER SCHEDULE. By default this is used for SCHEDULER JOBS, however I don't see any reason not using it for other purpose.

It would be this one:

CREATE OR REPLACE FUNCTION GetDueDate(start_date IN TIMESTAMP DEFAULT SYSTIMESTAMP, duration IN INTEGER DEFAULT 8) RETURN TIMESTAMP AS
    next_run_date TIMESTAMP := start_date;
BEGIN
    FOR i IN 1..duration LOOP
        DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING('FREQ=HOURLY;INTERVAL=1;BYHOUR=7,8,9,10,11,13,14,15,16,17;BYDAY=MON,TUE,WED,THU,FRI', NULL, next_run_date, next_run_date);
    END LOOP;
    RETURN next_run_date;
END;

Some tests:

BEGIN
DBMS_OUTPUT.PUT_LINE ( TO_CHAR(GetDueDate(TIMESTAMP '2015-04-10 16:20:00'), 'Day yyyy-mm-dd hh24:mi') );
DBMS_OUTPUT.PUT_LINE ( TO_CHAR(GetDueDate(TIMESTAMP '2015-04-11 07:20:00'), 'Day yyyy-mm-dd hh24:mi') );
DBMS_OUTPUT.PUT_LINE ( TO_CHAR(GetDueDate(TIMESTAMP '2015-04-13 07:20:00'), 'Day yyyy-mm-dd hh24:mi') );
DBMS_OUTPUT.PUT_LINE ( TO_CHAR(GetDueDate(TIMESTAMP '2015-04-13 09:20:00'), 'Day yyyy-mm-dd hh24:mi') );
DBMS_OUTPUT.PUT_LINE ( TO_CHAR(GetDueDate(TIMESTAMP '2015-04-13 11:20:00'), 'Day yyyy-mm-dd hh24:mi') );
DBMS_OUTPUT.PUT_LINE ( TO_CHAR(GetDueDate(TIMESTAMP '2015-04-13 17:20:00'), 'Day yyyy-mm-dd hh24:mi') );
END;

Monday    2015-04-13 14:20
Monday    2015-04-13 15:20
Monday    2015-04-13 16:20
Tuesday   2015-04-14 07:20
Tuesday   2015-04-14 09:20
Tuesday   2015-04-14 15:20

You can make it even more sophisticated and consider holidays, e.g.

BEGIN
    DBMS_SCHEDULER.CREATE_SCHEDULE('NEW_YEARS_DAY', repeat_interval => 'FREQ=YEARLY;INTERVAL=1;BYDATE=0101');
    DBMS_SCHEDULER.CREATE_SCHEDULE('MARTIN_LUTHER_KING_DAY', repeat_interval => 'FREQ=YEARLY;BYMONTH=JAN;BYDAY=3 FRI', comments => 'Third Monday of January');
    DBMS_SCHEDULER.CREATE_SCHEDULE('WASHINGTONS_BIRTHDAY', repeat_interval => 'FREQ=YEARLY;BYMONTH=FEB;BYDAY=3 MON', comments => 'Third Monday of February');
    DBMS_SCHEDULER.CREATE_SCHEDULE('MEMORIAL_DAY', repeat_interval => 'FREQ=YEARLY;BYMONTH=MAY;BYDAY=-1 MON', comments => 'Last Monday of May');
    DBMS_SCHEDULER.CREATE_SCHEDULE('INDEPENDENCE_DAY', repeat_interval => 'FREQ=YEARLY;INTERVAL=1;BYDATE=0704');
    DBMS_SCHEDULER.CREATE_SCHEDULE('CHRISTMAS_DAY', repeat_interval => 'FREQ=YEARLY;INTERVAL=1;BYDATE=1225');
    DBMS_SCHEDULER.CREATE_SCHEDULE('SPRING_BREAK', repeat_interval => 'FREQ=YEARLY;BYDATE=0301+SPAN:7D');
END;


CREATE OR REPLACE FUNCTION GetDueDate(start_date IN TIMESTAMP DEFAULT SYSTIMESTAMP, duration IN INTEGER DEFAULT 8) RETURN TIMESTAMP AS
    next_run_date TIMESTAMP := start_date;
BEGIN
    FOR i IN 1..duration LOOP
        DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING('FREQ=HOURLY;INTERVAL=1;BYHOUR=7,8,9,10,11,13,14,15,16,17;BYDAY=MON,TUE,WED,THU,FRI; EXCLUDE=NEW_YEARS_DAY,MARTIN_LUTHER_KING_DAY,WASHINGTONS_BIRTHDAY,MEMORIAL_DAY,INDEPENDENCE_DAY,CHRISTMAS_DAY,SPRING_BREAK', NULL, next_run_date, next_run_date);
    END LOOP;
    RETURN next_run_date;
END;

BEGIN
DBMS_OUTPUT.PUT_LINE ( TO_CHAR(GetDueDate(TIMESTAMP '2015-12-24 07:20:00'), 'Day yyyy-mm-dd hh24:mi') );
DBMS_OUTPUT.PUT_LINE ( TO_CHAR(GetDueDate(TIMESTAMP '2015-12-24 16:20:00'), 'Day yyyy-mm-dd hh24:mi') );
END;

Thursday  2015-12-24 16:20
Monday    2015-12-28 14:20

See calendaring syntax here: Calendaring Syntax