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\)]

Re: Holidays in SQL Server


"Nils Magnus Englund" <nils.magnus.en glund@orkfin.no > wrote in message
news:2rT%b.103$ 72.176991232@ne ws.telia.no...[color=blue]
> Hi!
>
> I have a large table in SQL Server 2000 with a datetime-column 'dt'. I[/color]
want[color=blue]
> to select all rows from that table, excluding days which fall on holidays[/color]
or[color=blue]
> 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[/color]
not[color=blue]
> in (select * from holidays)") , but I was looking for a better solution
> since that implies that I have to populate the "holidays" table.[/color]

That's probably your best idea.

Your holidays may not be mine.

[color=blue]
>
> Suggestions are welcome!
>
>
> Sincerely,
> Nils Magnus Englund
>
>[/color]

[Erland Sommarskog]

Re: Holidays in SQL Server

Nils Magnus Englund (nils.magnus.en glund@orkfin.no ) writes:[color=blue]
> 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.[/color]

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, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp