Oracle SQL 11g - next business day query

Question

Good day, I am working on some code where -

1) if a request was submitted after 5:00PM Eastern Timezone (America) on a weekday (Monday through Thursday) and before 7:59:59AM Eastern Timezone (America) on a weekday, than the date will be changed to the next business day @ 8:00 AM.

2) if a request was submitted between 5:00 PM on a Friday and before 7:59:59 AM that following Monday, than the time rolls up as noted above.

3) Then, the code needs to check another date field to compare an "accepted" time (within 4 hours of the submitted date or the "new" date).

For example:

If request 001 was submitted 04/17/2013 02:00AM, then its new date will be 04/17/2013 08:00AM.

If request 002 was submitted 04/17/2013 06:45PM, then its new date will be 04/18/2013 08:00AM.

If request 003 was submitted 04/20/2013 05:45AM (this is a Saturday), then its new date will be 04/22/2013 08:00AM.

I have been piecemailing the code together, in hopes that I can bring it all together in the final code.

---

This is what I have so far (for the date conversion code)

            ,CASE 
        WHEN to_date(('DATE_REQUESTED'),'DAY',nls_date_language = English) in ('Friday','Saturday'))
        THEN NEXT_DAY(to_date(('DATE_REQUESTED'),'Monday') + 8 / 24)
        ELSE DATE_REQUESTED 
    END as Weekend_Converted

---

For the code to check if the request was accepted within 4 hours

     SELECT RIT_Request_v.*
     ,CASE 
     WHEN DATE_ACCEPTED IS NULL THEN 'NOT ACKNOWLEDGED'
     WHEN DATE_ACCEPTED > (DATE_REQUESTED + 4 / 24) THEN 'OVER 4 HOURS'
     ELSE 'WITHIN 4 HOURS'
     END AS Acknowledgement

     FROM RIT.RIT_Request_v

     WHERE (("DATE_REQUESTED") BETWEEN trunc(sysdate, 'YYYY') AND trunc(sysdate))

---

As noted in one of my comments below, I updated my code, and am now getting "Invalid identifier" errors -

    SELECT RIT_Request_v.*
    ,CASE WHEN TO_CHAR(DATE_REQUESTED,'D') IN (1,6,7)
          THEN NEXT_DAY(DATE_REQUESTED,'MONDAY')
          ELSE DATE_REQUESTED + 1 END AS Weekend_Converted

    ,CASE WHEN DATE_ACCEPTED IS NULL THEN 'NOT ACKNOWLEDGED'
          WHEN WEEKEND_CONVERTED IS NULL THEN 'NOT ACKNOWLEDGED'
          WHEN DATE_ACCEPTED > (DATE_REQUESTED + 4 / 24) THEN 'OVER 4 HOURS'
          WHEN DATE_ACCEPTED > (Weekend_Converted + 4 / 24) THEN 'OVER 4 HOURS'
          ELSE 'WITHIN 4 HOURS' END AS Acknowledgement

    FROM RIT.RIT_Request_v

    WHERE (("DATE_REQUESTED") BETWEEN trunc(sysdate, 'YYYY') AND trunc(sysdate))

                AND FORM_ID IN         (2011,2014,5007,5036,5039,7007,10000,10001,10005,10007,10011,10024,10025,10029,10032,10033,10034,10035,10036,10037,11011,11013,11999,36001)

Answer 1

Your case generating the weekend_converted only seems to be dealing with the weekend in the original version, and seems to be confused as anything on Friday is included; the updated one is shifting every date to the same time either the next day or on the following Monday, losing both the 08:00 part and that for most of a weekday you don't shift the day at all.

You were getting 'invalid identifier' in your second version because you were defining the weekend_converted pseudocolumn and then trying to use it in the same level of query, which isn't allowed. You would need to define in it at one level, then refer to it in an outer select.

This seems to cover your rules, but I haven't tested it with any extra dates:

select r.*,
    case
        when to_char(date_requested, 'DY',
                'NLS_DATE_LANGUAGE=English') in ('SAT', 'SUN')
            or (to_char(date_requested, 'DY',
                'NLS_DATE_LANGUAGE=English') = 'FRI'
                and extract(hour from cast(date_requested as timestamp)) >= 17)
        then trunc(next_day(date_requested, 'MON')) + interval '8' hour
        when extract(hour from cast(date_requested as timestamp)) >= 17
        then trunc(date_requested) + interval '1' day + interval '8' hour
        when extract(hour from cast(date_requested as timestamp)) < 8
        then trunc(date_requested) + interval '8' hour
        else date_requested
    end as clock_start
from rit_request_v r;

Although, while forcing the NLS_DATE_LANGUAGE is a good idea if you don't know where this will run, the next_day function requires the day parameter to be in your local language, so you'd be better off setting it for the whole session rather than on a per-function basis.

Anyway, that gives:

DATE_REQUESTED   DATE_ACCEPTED       FORM_ID CLOCK_START
---------------- ---------------- ---------- ----------------
2013-04-17 02:00 2013-04-17 11:59       5007 2013-04-17 08:00
2013-04-17 18:45 2013-04-18 11:59       5007 2013-04-18 08:00
2013-04-13 05:45                        5007 2013-04-15 08:00

You can then use that as an inline view or as a CTE:

with t as (
select r.*,
    case
        when to_char(date_requested, 'DY',
                'NLS_DATE_LANGUAGE=English') in ('SAT', 'SUN')
            or (to_char(date_requested, 'DY',
                'NLS_DATE_LANGUAGE=English') = 'FRI'
                and extract(hour from cast(date_requested as timestamp)) >= 17)
        then trunc(next_day(date_requested, 'MON')) + interval '8' hour
        when extract(hour from cast(date_requested as timestamp)) >= 17
        then trunc(date_requested) + interval '1' day + interval '8' hour
        when extract(hour from cast(date_requested as timestamp)) < 8
        then trunc(date_requested) + interval '8' hour
        else date_requested
    end as clock_start
from rit_request_v r
where date_requested between trunc(sysdate, 'YYYY') and trunc(sysdate)
and form_id in (2011, 2014, 5007, 5036, 5039, 7007, 10000, 10001, 10005, 10007, 10011, 10024, 10025, 10029, 10032, 10033, 10034, 10035, 10036, 10037, 11011, 11013, 11999, 36001)
)
select t.*,
    case
        when date_accepted is null then 'NOT ACKNOWLEDGED'
        when date_accepted > (clock_start + interval '4' hour)
        then 'OVER 4 HOURS'
        else 'WITHIN 4 HOURS'
    end as acknowledgement
from t;

Which gives:

DATE_REQUESTED   DATE_ACCEPTED       FORM_ID CLOCK_START      ACKNOWLEDGEMENT
---------------- ---------------- ---------- ---------------- ----------------
2013-04-17 02:00 2013-04-17 11:59       5007 2013-04-17 08:00 WITHIN 4 HOURS
2013-04-17 18:45 2013-04-18 11:59       5007 2013-04-18 08:00 WITHIN 4 HOURS
2013-04-13 05:45                        5007 2013-04-15 08:00 NOT ACKNOWLEDGED

I can refer to clock_start because it's defined at a lower level, in this case in the CTE.

You'll notice that my dates are in a completely different format to yours. Normally I'd say you should wrap those fields in a to_char() for display, but (a) it isn't clear if this will be displayed in SQL*Plus or wherever, or will feed into something else and should stay as dates; and if you do alter the session to check the NLS_DATE_LANGUAGE then you could set NLS_DATE_FORMAT while you're there. Again depends where an dhow you're running it.

You might also have the check against sysdate at the wrong level. As it is, it will include any that have a clock_start of 08:00 today, since it's working on the pre-adjustment time. If you move that to the outer select then you can check that clock_start is in that date range instead. You might also have done that intentionally, of course.

Hope that helps...