I am trying to write a query that takes a table which contains 2 dates deadline and dateentered. If deadline has a value this is calculated as the deadline
otherwise the deadline is calculated as the DateEntered + 20 working days. I have a calendar table populated with dates for the next few years on my test server.
Ideally, I would like to this in a set based way without using any UDFs or cursors but I'm not sure if it is possible.
My current best attempt is below. However, it suffers from a problem in that if the Deadline is 2013-01-02 00:00:00.000 it will return 2013-01-04 00:00:00.000 instead of 2013-01-02 00:00:00.000. If anyone can offer advice, links, suggestions etc that would be fantastic.
drop table #LocalTempTable
drop table #Calendar
CREATE TABLE #LocalTempTable(
create table #Calendar(
insert into #LocalTempTable values (null,'2013-01-01')
insert into #LocalTempTable values ('2013-01-02',null)
insert into #Calendar values ('2013-01-01',0)
insert into #Calendar values ('2013-01-02',0)
insert into #Calendar values ('2013-01-03',0)
insert into #Calendar values ('2013-01-04',0)
insert into #Calendar values ('2013-01-05',0)
insert into #Calendar values ('2013-01-06',0)
select * from #LocalTempTable a
select [CalendarDate] as ResponseDate FROM (SELECT [CalendarDate], N = ROW_NUMBER() OVER (ORDER BY [CalendarDate])
WHERE Weekend = 0 /*AND [CalendarDate] > w.[DateEntered]*/ ) a WHERE N = 4
order by DateEntered desc