|
|
Posted by Hugo Kornelis on 11/30/06 21:49
On 30 Nov 2006 05:34:29 -0800, paulmac106@gmail.com wrote:
>Hi.
>
>I utilize the Calendar table, and I'm able to find how many working
>days between 2 dates, but does anyone use this table to find the 2nd or
>5th working date?
>
>if 11/30/06 then 12/4/06
>
>I'm sure it's not too difficult but i can't seem to get it to work
>
>(select caldate from calendar where...?...and workingday = 'Y')
Hi Paul,
Basics first: here's a query to get the next working day after @StartDt.
SELECT TOP (1) TheDate
FROM Calendar
WHERE TheDate > @StartDt
AND WorkingDay = 'Y'
ORDER BY TheDate;
Unfortunately, we need to add a bit more complexity for the second
business day: first, we get the TWO next business days, then pick the
last of them:
SELECT TOP (1) TheDate
FROM (SELECT TOP (2) TheDate
FROM Calendar
WHERE TheDate > @StartDt
AND WorkingDay = 'Y'
ORDER BY TheDate) AS d
ORDER BY TheDate DESC;
This can be easily adapted to get the third, fourth, etc. working day:
just replace TOP (2) with TOP (3), TOP (4), etc.
Note 1: If on SQL Server 2000, replace TOP (1) and TOP (2) with TOP 1
and TOP 2.
Note 2: If on SQL Server 2005, you may also use TOP (@NumOfDays) to make
the number of business days to go forward variable.
--
Hugo Kornelis, SQL Server MVP
Navigation:
[Reply to this message]
|