Holidays in SQL Server

[Nils Magnus Englund]

Hi!

I have a large table in SQL Server 2000 with a datetime-column 'dt'. I want
to select all rows from that table, excluding days which fall on holidays or
weekends. What is the best way to accomplish this? I considered creating a
new table called "holidays" and then selecting all rows (sort of "where not
in (select * from holidays)") , but I was looking for a better solution
since that implies that I have to populate the "holidays" table.

Suggestions are welcome!
Sincerely,
Nils Magnus Englund

[Greg D. Moore \(Strider\)]

"Nils Magnus Englund" <ni************ *****@orkfin.no > wrote in message
news:2r******** ************@ne ws.telia.no...

Hi!

I have a large table in SQL Server 2000 with a datetime-column 'dt'. I want to select all rows from that table, excluding days which fall on holidays or weekends. What is the best way to accomplish this? I considered creating a
new table called "holidays" and then selecting all rows (sort of "where not in (select * from holidays)") , but I was looking for a better solution
since that implies that I have to populate the "holidays" table.
That's probably your best idea.

Your holidays may not be mine.


Suggestions are welcome!
Sincerely,
Nils Magnus Englund

[Erland Sommarskog]

Nils Magnus Englund (ni************ *****@orkfin.no ) writes:

I have a large table in SQL Server 2000 with a datetime-column 'dt'. I
want to select all rows from that table, excluding days which fall on
holidays or weekends. What is the best way to accomplish this? I
considered creating a new table called "holidays" and then selecting all
rows (sort of "where not in (select * from holidays)") , but I was
looking for a better solution since that implies that I have to populate
the "holidays" table.

And how would you expect SQL Server to know about syttende maj or when
Midsummer is?

You can of course make the holidays table more or less sophisticated.
You can just put in all Mondays to Fridays that are not dates from now
to 2020 or whatever.

You can also write a stored procedure that fills in the table given the
rules about currently known holidays. You would need to find data on
where Easter falls, to determine days for Easter, Whitsun and Ascenion Day.

Yet an alternative is to put all days in that table, and then a flag
whether the day is a working day or not, no matter whether it's Friday
or Sunday.

And finally, for the SELECT it self I prefer:

SELECT *
FROM tbl t
WHERE NOT EXISTS (SELECT *
FROM holidays h
WHERE t.date = h.date)
--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp