sql - Add X number of Working days to a date -

i have table postingperiod uses company calendar track working days. simplified, looks this:

date        year quarter month day isworkingday 25.06.2015  2015 2       6     25  1 26.06.2015  2015 2       6     26  1 27.06.2015  2015 2       6     27  0 

i have table contains purchase lines orderdate, confirmed delivery date vendor , maximum allowed timeframe in working days between orderdate , deliverydate:

purchid  orderdate  confdelivery  deliverydays 1234     14.04.2015 20.05.2015    30 1235     14.04.2015 24.05.2015    20 

i want create new column returns maximum allowed date (regardless of workday or not) each order. usual approach (workingdays / 5 weeks, multiplied 7 days) doesn't work, holidays etc need taken consideration. dwh feed olap database, performance not issue.

you assigning each working day arbitrary index using row_number, e.g.

select  date, workingdayindex = row_number() over(order date)    dbo.calendar 

which give like:

date        workingdayindex ----------------------------- 2015-04-27      80 2015-04-28      81 2015-04-29      82 2015-04-30      83 2015-05-01      84 2015-05-05      85 2015-05-06      86 2015-05-07      87 

then if want know date n working days given date, find date index n higher, i.e. 2015-04-27 has index of 80, therefore 5 working days later have index of 85 yields 2015-05-05.

full working example

/***************************************************************************************************************************/ -- create tables , populate test data set datefirst 1; declare @calendar table (date date, isworkingday bit); insert @calendar select  top 365 dateadd(day, row_number() over(order object_id), '20141231'), 1 sys.all_objects; update  @calendar set     isworkingday = 0   datepart(weekday, date) in (6, 7) or      date in ('2015-01-01', '2015-04-03', '2015-04-06', '2015-05-04', '2015-05-25', '2015-08-31', '2015-12-25', '2015-12-28'); declare @t table (purchid int, orderdate date, confdeliverydate date, deliverydays int); insert @t values (1234, '20150414', '20150520', 30), (1235, '20150414', '20150524', 20);  /***************************************************************************************************************************/ -- actual query workingdaycalendar (   select  *, workingdayindex = row_number() over(order date)        @calendar       isworkingday = 1 ) select  *    @t t         inner join workingdaycalendar c1             on c1.date = t.orderdate         inner join workingdaycalendar c2             on c2.workingdayindex = c1.workingdayindex + t.deliverydays; 

if common requirement, make workingdayindex fixed field on calendar table don't need calculate each time required.