sql query help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

sql query help

Author

Topic

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2012-02-27 : 11:07:51

How can I count the number of hours between 2 dates, but exclude weekends and holidays.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-02-27 : 11:15:53

First thing you need is a table that defines what days are holidays. Do you have one?

--
Gail Shaw
SQL Server MVP

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2012-02-27 : 11:22:47

Thanks Gila

I don't have table that defines what days are holidays.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-02-27 : 11:28:41

they are very similar to number tables (like the other thread)
If you make a calendar table of days (All days from whenever to 100 + years in the future is good).

For each day you could have a column saying whether is a holiday / weekend etc.

Then all you need to do is JOIN to the calender table and exclude the weekend / holidays.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-02-27 : 12:13:35

SQL has no idea whether a day is a holiday or not, so you need a table that defines what days are holidays. Preferably a calendar table as Charlie describes.

--
Gail Shaw
SQL Server MVP

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-27 : 13:05:42

Holidays are unique to a country, province, state or region..there is very little that is global...ok, maybe not little, but significant enough

For example..Ireland has "Bank" Holidays...every other monday or so..that no one else has...coulda just called Irish Flu Monday

Here's how you can find the Number of Days..then just Multiple by 24

Then only thing then is to interrogate your caledar table and find the number of holidays between and subtract that

DECLARE @d1 datetime, @d2 datetime
SELECT @d1 = '9/11/2001', @d2 = GETDATE()
SELECT DATEDIFF(dd,@d1,@d2)

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2012-02-27 : 14:26:19

Thanks i will creata a calendar table..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources