CODEWITHSUNDEEP
sqloracle-databasedateoracle10g
Matthew Hart
Matthew Hart

Reputation: 359

Oracle SQL: Find the date 7 days ahead of a specified date excluding Sundays and other specific dates

I need to find what the date is 7 days ahead of any given date. The start date can be any date, however, the end date should not be a Sunday or a day marked as a holiday. There can only ever be 2 consecutive holiday days.

I have code that works, however, it's very long-winded. Is there potentially a simpler solution?

Also, if the required date range changed at some point in the future or the possible number of consecutive holidays changed, the script would need to be updated in multiple places, if this could be reduced then that would be great.

I already have a table (CALENDAR) containing a substantial range of dates, their day of the week and whether the date is considered a holiday. Something like this:

START_DATE, DAY_OF_WEEK, HOLIDAY
10-DEC-17 , Sun        , 0
11-DEC-17 , Mon        , 0
12-DEC-17 , Tue        , 0
13-DEC-17 , Wed        , 0
14-DEC-17 , Thu        , 0
15-DEC-17 , Fri        , 0
16-DEC-17 , Sat        , 0
17-DEC-17 , Sun        , 0
18-DEC-17 , Mon        , 0
19-DEC-17 , Tue        , 0
20-DEC-17 , Wed        , 0
21-DEC-17 , Thu        , 0
22-DEC-17 , Fri        , 0
23-DEC-17 , Sat        , 0
24-DEC-17 , Sun        , 0
25-DEC-17 , Mon        , 1
26-DEC-17 , Tue        , 1
27-DEC-17 , Wed        , 0
28-DEC-17 , Thu        , 0
29-DEC-17 , Fri        , 0
30-DEC-17 , Sat        , 0
31-DEC-17 , Sun        , 0
01-JAN-18 , Mon        , 1
etc...

The expected OUTPUT would be something like:

START_DATE, END_DATE
10-DEC-17,  18-DEC-17
11-DEC-17,  18-DEC-17
12-DEC-17,  19-DEC-17
13-DEC-17,  20-DEC-17
14-DEC-17,  21-DEC-17
15-DEC-17,  22-DEC-17
16-DEC-17,  23-DEC-17
17-DEC-17,  27-DEC-17
18-DEC-17,  27-DEC-17
19-DEC-17,  27-DEC-17
20-DEC-17,  27-DEC-17
21-DEC-17,  28-DEC-17
22-DEC-17,  29-DEC-17
23-DEC-17,  30-DEC-17
24-DEC-17,  02-JAN-18
25-DEC-17,  02-JAN-18
26-DEC-17,  02-JAN-18
27-DEC-17,  03-JAN-18
28-DEC-17,  04-JAN-18
etc...

Below is my existing code. My approach is that as there can only ever be 3 consecutive excluded days (a Sunday followed by 2 holiday days), then I check 4 days ahead for each date and then take the first one which is not an excluded date. One of the 4 should always be a valid end date.

with temp as
(
select
    start_date,
    case    
        when lead(day_of_week, 7) over(order by start_date) = 'Sun'
          or lead(holiday, 7) over(order by start_date) = 1
        then null
    else
        lead(start_date, 7) over(order by start_date)
    end as days_7,
    case    
        when lead(day_of_week, 8) over(order by start_date) = 'Sun'
          or lead(holiday, 8) over(order by start_date) = 1
        then null
    else
        lead(start_date, 8) over(order by start_date)
    end as days_8,
    case    
        when lead(day_of_week, 9) over(order by start_date) = 'Sun'
          or lead(holiday, 9) over(order by start_date) = 1
        then null
    else
        lead(start_date, 9) over(order by start_date)
    end as days_9,
        case    
        when lead(day_of_week, 10) over(order by start_date) = 'Sun'
          or lead(holiday, 10) over(order by start_date) = 1
        then null
    else
        lead(start_date, 10) over(order by start_date)
    end as days_10
from
    calendar
)

select
    start_date,
    COALESCE(days_7, days_8, days_9, days_10) as end_date 
from
    temp

Upvotes: 0

Views: 250

Answers (2)

PKey
PKey

Reputation: 3841

given holidays table: 

with holidays as ( 
select to_date('10-DEC-17','dd-MON-YY') as mydate , 'Sun' as myday, 0 holiday from dual
union all
select to_date('11-DEC-17','dd-MON-YY') as mydate , 'Mon' as myday, 0 holiday from dual
union all
select to_date('12-DEC-17','dd-MON-YY') as mydate , 'Tue' as myday, 0 holiday from dual
union all
select to_date('13-DEC-17','dd-MON-YY') as mydate , 'Wed' as myday, 0 holiday from dual
union all
select to_date('14-DEC-17','dd-MON-YY') as mydate , 'Thu' as myday, 0 holiday from dual
union all
select to_date('15-DEC-17','dd-MON-YY') as mydate , 'Fri' as myday, 0 holiday from dual
union all
select to_date('16-DEC-17','dd-MON-YY') as mydate , 'Sat' as myday, 0 holiday from dual
union all
select to_date('17-DEC-17','dd-MON-YY') as mydate , 'Sun' as myday, 0 holiday from dual
union all
select to_date('18-DEC-17','dd-MON-YY') as mydate , 'Mon' as myday, 0 holiday from dual
union all
select to_date('19-DEC-17','dd-MON-YY') as mydate , 'Tue' as myday, 0 holiday from dual
union all
select to_date('20-DEC-17','dd-MON-YY') as mydate , 'Wed' as myday, 0 holiday from dual
union all
select to_date('21-DEC-17','dd-MON-YY') as mydate , 'Thu' as myday, 0 holiday from dual
union all
select to_date('22-DEC-17','dd-MON-YY') as mydate , 'Fri' as myday, 0 holiday from dual
union all
select to_date('23-DEC-17','dd-MON-YY') as mydate , 'Sat' as myday, 0 holiday from dual
union all
select to_date('24-DEC-17','dd-MON-YY') as mydate , 'Sun' as myday, 0 holiday from dual
union all
select to_date('25-DEC-17','dd-MON-YY') as mydate , 'Mon' as myday, 1 holiday from dual
union all
select to_date('26-DEC-17','dd-MON-YY') as mydate , 'Tue' as myday, 1 holiday from dual
union all
select to_date('27-DEC-17','dd-MON-YY') as mydate , 'Wed' as myday, 0 holiday from dual
union all
select to_date('28-DEC-17','dd-MON-YY') as mydate , 'Thu' as myday, 0 holiday from dual
union all
select to_date('29-DEC-17','dd-MON-YY') as mydate , 'Fri' as myday, 0 holiday from dual
union all
select to_date('30-DEC-17','dd-MON-YY') as mydate , 'Sat' as myday, 0 holiday from dual
union all
select to_date('31-DEC-17','dd-MON-YY') as mydate , 'Sun' as myday, 0 holiday from dual
union all
select to_date('01-JAN-18','dd-MON-YY') as mydate , 'Mon' as myday, 1 holiday from dual),

Here I find working days and then use first row to get the desired date 

working as (
select holidays.*, case when myday='Sun' or holiday=1 
 then 0 else 1 end as working_day  from holidays
)
select  mydate from (
select mydate from working where 
   mydate>=to_date('21/12/2017','dd/mm/rrrr')+7
   order by (case when working_day=0 then null  else mydate end) asc nulls last
) where rownum=1

Upvotes: 0

MT0
MT0

Reputation: 168281

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE Calendar( START_DATE, HOLIDAY ) AS
SELECT TO_DATE( '10-DEC-17', 'DD-MON-YY' ), 0 FROM DUAL UNION ALL
SELECT TO_DATE( '11-DEC-17', 'DD-MON-YY' ), 0 FROM DUAL UNION ALL
SELECT TO_DATE( '12-DEC-17', 'DD-MON-YY' ), 0 FROM DUAL UNION ALL
SELECT TO_DATE( '13-DEC-17', 'DD-MON-YY' ), 0 FROM DUAL UNION ALL
SELECT TO_DATE( '14-DEC-17', 'DD-MON-YY' ), 0 FROM DUAL UNION ALL
SELECT TO_DATE( '15-DEC-17', 'DD-MON-YY' ), 0 FROM DUAL UNION ALL
SELECT TO_DATE( '16-DEC-17', 'DD-MON-YY' ), 0 FROM DUAL UNION ALL
SELECT TO_DATE( '17-DEC-17', 'DD-MON-YY' ), 0 FROM DUAL UNION ALL
SELECT TO_DATE( '18-DEC-17', 'DD-MON-YY' ), 0 FROM DUAL UNION ALL
SELECT TO_DATE( '19-DEC-17', 'DD-MON-YY' ), 0 FROM DUAL UNION ALL
SELECT TO_DATE( '20-DEC-17', 'DD-MON-YY' ), 0 FROM DUAL UNION ALL
SELECT TO_DATE( '21-DEC-17', 'DD-MON-YY' ), 0 FROM DUAL UNION ALL
SELECT TO_DATE( '22-DEC-17', 'DD-MON-YY' ), 0 FROM DUAL UNION ALL
SELECT TO_DATE( '23-DEC-17', 'DD-MON-YY' ), 0 FROM DUAL UNION ALL
SELECT TO_DATE( '24-DEC-17', 'DD-MON-YY' ), 0 FROM DUAL UNION ALL
SELECT TO_DATE( '25-DEC-17', 'DD-MON-YY' ), 1 FROM DUAL UNION ALL
SELECT TO_DATE( '26-DEC-17', 'DD-MON-YY' ), 1 FROM DUAL UNION ALL
SELECT TO_DATE( '27-DEC-17', 'DD-MON-YY' ), 0 FROM DUAL UNION ALL
SELECT TO_DATE( '28-DEC-17', 'DD-MON-YY' ), 0 FROM DUAL UNION ALL
SELECT TO_DATE( '29-DEC-17', 'DD-MON-YY' ), 0 FROM DUAL UNION ALL
SELECT TO_DATE( '30-DEC-17', 'DD-MON-YY' ), 0 FROM DUAL UNION ALL
SELECT TO_DATE( '31-DEC-17', 'DD-MON-YY' ), 0 FROM DUAL UNION ALL
SELECT TO_DATE( '01-JAN-18', 'DD-MON-YY' ), 1 FROM DUAL;

Query 1:

SELECT start_date,
       LEAD( end_date, 7 ) OVER ( ORDER BY start_date ) AS end_date
FROM   (
  SELECT start_date,
         LAST_VALUE(
           CASE
           WHEN Holiday = 0
           AND  start_date - TRUNC( start_date, 'IW' ) < 6
           THEN start_date
           END
         ) IGNORE NULLS OVER ( ORDER BY start_date DESC ) AS End_date
  FROM   Calendar
)

Results:

|           START_DATE |             END_DATE |
|----------------------|----------------------|
| 2017-12-10T00:00:00Z | 2017-12-18T00:00:00Z |
| 2017-12-11T00:00:00Z | 2017-12-18T00:00:00Z |
| 2017-12-12T00:00:00Z | 2017-12-19T00:00:00Z |
| 2017-12-13T00:00:00Z | 2017-12-20T00:00:00Z |
| 2017-12-14T00:00:00Z | 2017-12-21T00:00:00Z |
| 2017-12-15T00:00:00Z | 2017-12-22T00:00:00Z |
| 2017-12-16T00:00:00Z | 2017-12-23T00:00:00Z |
| 2017-12-17T00:00:00Z | 2017-12-27T00:00:00Z |
| 2017-12-18T00:00:00Z | 2017-12-27T00:00:00Z |
| 2017-12-19T00:00:00Z | 2017-12-27T00:00:00Z |
| 2017-12-20T00:00:00Z | 2017-12-27T00:00:00Z |
| 2017-12-21T00:00:00Z | 2017-12-28T00:00:00Z |
| 2017-12-22T00:00:00Z | 2017-12-29T00:00:00Z |
| 2017-12-23T00:00:00Z | 2017-12-30T00:00:00Z |
| 2017-12-24T00:00:00Z |               (null) |
| 2017-12-25T00:00:00Z |               (null) |
| 2017-12-26T00:00:00Z |               (null) |
| 2017-12-27T00:00:00Z |               (null) |
| 2017-12-28T00:00:00Z |               (null) |
| 2017-12-29T00:00:00Z |               (null) |
| 2017-12-30T00:00:00Z |               (null) |
| 2017-12-31T00:00:00Z |               (null) |
| 2018-01-01T00:00:00Z |               (null) |

Upvotes: 2

Related Questions