Holidays in SQL Server

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 EnglundCreating a holidays/calendar table is a good thing. This will surely simplify
your search. Remember, your holidays might not be the same as mine so having the
table will eliminate such.
--
-oj
http://www.rac4sql.net
"Nils Magnus Englund" <nils.magnus.englund@.orkfin.no> wrote in message
news:ujHI8Za$DHA.808@.TK2MSFTNGP12.phx.gbl...
> 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
>|||I think it is a good idea to have a calendar table.
--
Roji. P. Thomas
SQL Server Programmer
"Nils Magnus Englund" <nils.magnus.englund@.orkfin.no> wrote in message
news:ujHI8Za$DHA.808@.TK2MSFTNGP12.phx.gbl...
> 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
>|||select * from TableName where DATETIME not in (select DATETIME from holidays
"Nils Magnus Englund" <nils.magnus.englund@.orkfin.no> ¼¶¼g©ó¶l¥ó·s»D
:ujHI8Za$DHA.808@.TK2MSFTNGP12.phx.gbl...
> 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
>|||Use a calendar table. See the "more advanced example" at
http://www.aspfaq.com/2453
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Nils Magnus Englund" <nils.magnus.englund@.orkfin.no> wrote in message
news:ujHI8Za$DHA.808@.TK2MSFTNGP12.phx.gbl...
> 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
>