Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Advice on trying to write set based query which adds working days to a date Expand / Collapse
Posted Wednesday, November 20, 2013 2:18 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 21, 2013 5:21 PM
Points: 1, Visits: 43
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
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

Post #1516208
Posted Wednesday, November 20, 2013 4:35 PM



Group: General Forum Members
Last Login: Thursday, June 23, 2016 11:33 AM
Points: 2,137, Visits: 7,463
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


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

  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw
  • Post #1516259
    « Prev Topic | Next Topic »

    Add to briefcase

    Permissions Expand / Collapse