sql server (alternate): 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

Worked perfectly!

thanks Hugo, I really appreciate it.

Worked perfectly!

thanks Hugo, I really appreciate it.

(paulmac106@gmail.com) writes:
[quoted text, click to view]

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/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at

[quoted text, click to view]

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.

--

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.