Find 2nd Working Day

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')

thanks,
PaulOn 30 Nov 2006 05:34:29 -0800, paulmac106@.gmail.com wrote:

Quote:

Originally Posted by

>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|||Worked perfectly!

thanks Hugo, I really appreciate it.|||Worked perfectly!

thanks Hugo, I really appreciate it.|||(paulmac106@.gmail.com) writes:

Quote:

Originally Posted by

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')

If this is a common operation, adding a business-dayno column to the
table can be a good idea.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Add a julianized business column to your table and the math is very
simple. Look at the current day's Julianized number, subtract and
return the MIN(cal_date) with that julian_business_day value.

CREATE TABLE Calendar
(cal_date DATETIME NOT NULL PRIMARY KEY,
julian_business_day INTEGER NOT NULL,
..);

etc.
INSERT INTO Calendar VALUES ('2006-12-01', 10); -- fri
INSERT INTO Calendar VALUES ('2006-12-02, 10); -- sat
INSERT INTO Calendar VALUES ('2006-12-01', 10); -- sun
INSERT INTO Calendar VALUES ('2006-12-01', 11); -- mon
INSERT INTO Calendar VALUES ('2006-12-01', 12); -- tue
etc.