Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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
Author
Message
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
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

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


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 5:39 PM
Points: 1,805, Visits: 5,874
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


  • MMGrid Addin
  • MMNose Addin


  • 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