CODEWITHSUNDEEP
sqloracle-databaseplsql
Javad Kargar
Javad Kargar

Reputation: 1425

Next workday that not holiday in sql

I have Holiday table that contains all holidays in it.

How can I get next work day from given date in one SQL query and not use FOR Loop like below?

DECLARE
    givendate   DATE := TO_DATE('2019-07-01', 'YYYY-MM-DD');
    out         NUMBER := 1;  
BEGIN
  WHILE out != 0 LOOP
    SELECT
        COUNT(*)
    INTO out
    FROM
        holiday h
    WHERE
        trunc(h.holiday_date) = trunc(givendate);
    IF out != 0 THEN
        givendate := givendate + 1;
    END IF;
  END LOOP;
  DBMS_OUTPUT.put_line(givendate);
END;

Upvotes: 0

Views: 211

Answers (2)

nick
nick

Reputation: 693

SELECT MIN(hd)
FROM (
    select TO_DATE('2019-07-01', 'YYYY-MM-DD') as hd from dual
    union 
    select trunc(h.holiday_date + 1)
        FROM
            holiday h
        WHERE
            trunc(h.holiday_date) >= TO_DATE('2019-07-01', 'YYYY-MM-DD')
            )  t left join holiday h2 on trunc(h2.holiday_date) = t.hd
WHERE h2.holiday_date IS NULL;

Upvotes: 2

Dmitry Grekov
Dmitry Grekov

Reputation: 688

You may want to use a hierarchical query:

 select  nvl(max(holiday_date)+1, trunc(sysdate))
   from  holiday
connect  by holiday_date = prior holiday_date + 1
  start  with holiday_date = trunc(sysdate)

It works like that:

  • if sysdate is a holiday, it builds a chain of continuous holidays and then returns max holiday_date + 1 as thenext business date

  • if sysdate is not a holiday, it returns sysdate (nvl() does that when max returns null)

So just replace trunc(sysdate) with your given_date and it should work just as your piece of code.

Upvotes: 1

Related Questions