plsql - Get nth working date in Oracle -

we have function in sql calculates nth working date excluding weekends , holidays, when pass startdate, enddate , workday(nth).. gives nth working day...

working sql function:

function [dbo].[getnthworkingdate]     ( @startdate datetime, @enddate datetime, @workday int   )     returns datetime         begin -- declare return variable here declare @workdate datetime, @leaveyear smallint,@icount int set @leaveyear = datepart(year, @startdate) set @icount = 1 while (@startdate < @enddate)      begin             if (datename(weekday,@startdate )  = 'sunday') or (datename(weekday,@startdate )  = 'saturday')             -- keep if statement out code             set @icount = @icount;         else if exists (select * holidays cast (holiday + ' ' + cast(@leaveyear varchar) datetime) = @startdate)             -- keep if statement out code             set @icount = @icount;         else             begin                  set @workdate = @startdate                 if @icount = @workday                     break;                  else                     set @icount = @icount + 1;             end           set @startdate = dateadd(day, 1, @startdate );     end  -- return result of function return @workdate end 

i trying recreate function in oracle(new oracle), have made few changes couldn't working, think missing in looping, appreciated... in advance..

oracle function:

create or replace function getnthworkingdate (pstartdate date,                                           penddate date,                                           pworkday number)   return date     vstartdate date;   vworkdate date ;   vcount number;   vholiday date;   begin    vcount := 1;   begin   select holiday_date vholiday holiday (to_char(holiday_date, 'mm dd') = to_char(pstartdate, 'mm dd'));   exception    when no_data_found            vholiday := null;     end;      vstartdate := pstartdate;  begin while (vstartdate < penddate) loop     if (to_char(vstartdate, 'day') = 'sunday' ) or (to_char(vstartdate, 'day') = 'saturday')       vcount := vcount;    elsif (to_char(vholiday, 'mm dd') = to_char(vstartdate, 'mm dd'))       vcount := vcount;    else             vworkdate := vstartdate;        if vcount = pworkday         exit;       else         vcount := vcount + 1;                 end if;   end if;       vstartdate := vstartdate + 1; end loop;  end;  return vworkdate;  end getnthworkingdate; 

something works - in plain sql. if don't need use pl/sql, better off not using it. if need use it, adapt needed. see in comments changed requirement; adapt need.

the "magic number" 2 * :wd_number + 5 there make sure add enough calendar dates include @ least :wd_number workdays; +5 low values of :wd_number. not efficient solution, it's not wasting more few milliseconds didn't bother make more efficient.

with holidays( holiday_date, holiday_name ) (        select date '2016-01-01', 'new year''s day'   dual union        select date '2016-04-01', 'april fools'' day' dual union        select date '2016-05-01', 'may first'         dual      ),      work_days ( dt ) (        select  to_date(:start_date, 'yyyy-mm-dd') + level - 1           dual          to_char(to_date(:start_date, 'yyyy-mm-dd') + level - 1, 'dy')                                                                      not in ('sat', 'sun')          connect level < 2 * to_number(:wd_number) + 5        minus        select  holiday_date            holidays      ),      ordered_work_days ( dt, rn ) (        select dt, row_number() on (order dt)          work_days      ) select dt   ordered_work_days  rn = to_number(:wd_number) ;