2

I have a task to find the next business day of particular given date. It should exclude the holidays that are listed in holiday table (mst_holiday). I have a procedure using decode function to find the next business day when there is continuous holiday or weekend. For example if today is 21-07-2013 Wednesday, if I need to find 5 days later it should display 26-07-2013, if it is a holiday, it should display the successive non holiday.

The sample decode function I have used is below. Please help me to write alternate sql query.

DECODE
   (ldate,
    laps.holidaydate, DECODE
       (ldate + 1,
        laps.holidaydate + 1, DECODE
           (ldate + 2,
            laps.holidaydate + 2,ldate+3,ldate+2),ldate+1),ldate);`
Alex Poole
  • 183,384
  • 11
  • 179
  • 318
Gowtham VJ
  • 97
  • 1
  • 3
  • 13
  • Check out this answer, seems like a ready function : http://stackoverflow.com/a/16413949/1026199 – eric.itzhak Aug 21 '13 at 08:49
  • 4
    You've got answers on the previous version of the question. This doesn't seem to add anything new. You should explain why those answers aren't useful and modify your original question to give the details requested and more information, not ask the same thing again. – Alex Poole Aug 21 '13 at 08:55

1 Answers1

0

Do this 3 steps:

1) Generate a range of dates you want to check for example:

select to_date(:start_date,'dd-mon-yyyy') + rownum -1
from all_objects
where rownum <= to_date(:end_date,'dd-mon-yyyy')-to_date(:start_date,'dd-mon-yyyy')+1

2) Select minus the table with the holidays table

3) Select ordered by date and get the first record

Put it all in one sql Select.

Miguel Prz
  • 13,718
  • 29
  • 42
  • what does this end date mean? how to minus with holiday table? – Gowtham VJ Aug 21 '13 at 09:02
  • limits the range of dates between you must make sure there is at least 1 bussines day. You could substitute by "where rownum<7". Select MINUS substract the results of select from another: http://docs.oracle.com/cd/B19306_01/server.102/b14200/queries004.htm – Miguel Prz Aug 21 '13 at 09:13