Find 2nd Working Day

[paulmac106]

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,
Paul

[Hugo Kornelis]

On 30 Nov 2006 05:34:29 -0800, pa********@gmai l.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

[paulmac106]

Worked perfectly!

thanks Hugo, I really appreciate it.

[paulmac106]

Worked perfectly!

thanks Hugo, I really appreciate it.

[Erland Sommarskog]

(pa********@gma il.com) writes:

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, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[--CELKO--]

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.