Advice on trying to write set based query which adds working days to a date

• ahemingway

  Newbie

  Points: 1

  More actions

  November 20, 2013 at 2:18 pm

  #100540

  Hi,

  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(

  Deadline datetime,

  DateEntered datetime

  )

  create table #Calendar(

  CalendarDate datetime,

  Weekend bit

  )

  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

  CROSS APPLY

  (

  select [CalendarDate] as ResponseDate FROM (SELECT [CalendarDate], N = ROW_NUMBER() OVER (ORDER BY [CalendarDate])

  FROM #Calendar

  WHERE Weekend = 0 /*AND [CalendarDate] > w.[DateEntered]*/ ) a WHERE N = 4

  ) b

  order by DateEntered desc

• mister.magoo

  SSC-Forever

  Points: 47068

  More actions

  November 20, 2013 at 4:35 pm

  #1667969

  Try this

  select isnull(ltt.Deadline,x.CalendarDate)

  from #LocalTempTable AS ltt

  cross apply (

  select max(calc.CalendarDate) AS CalendarDate

  from (

  select top 20 cal.CalendarDate

  from #Calendar AS cal

  where CalendarDate>=DateEntered

  and Weekend=0

  order by CalendarDate

  ) calc

  ) x

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]