How to use a holiday table - 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 2005 Forums

Transact-SQL (2005)

How to use a holiday table

Author

Topic

rwaldron
Posting Yak Master

131 Posts

Posted - 2009-11-11 : 06:27:41

Hi all,
I have 2 tables in SQL tbldata tblholidays.
tbldata contains date_received and date_completed
tblholidays contains a list of dates (column is called FullDate) that are to be excluded from calculation.

I want to calulate workdays (Mon-Fri) between date_received and date_completed and EXCLUDE dates from holiday table...in calculation

I need to do this with/without variables? ie I need the calculation based on columns...is this even possible..

I have previously used a function to calculate the workdays but I can't figure out how to then exclude the holidays..

something like
functwordays(date_booked,date_completed)

will give me workdays between date_booked and date_completed

then maybe
functwordays(date_booked,date_completed) - (select count (*)
from tblholidays where FullDate between date_booked and date_complete

Am I able to set a variable = column date_booked ?
ie: @date_booked ?

Please help..

Ray..

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-11-11 : 07:54:00

Do you have a calendar table ?

KH
[spoiler]Time is always against us[/spoiler]

rwaldron
Posting Yak Master

131 Posts

Posted - 2009-11-11 : 10:33:42

Hiya,
I got it in the end.
I'm not using a calendar table as I can't find one to suit my needs.
I used a holidays tbl instead..
Holidays table contains dates that are considered non working days

so the following is similar to what I needed to do.

functwordays(date_booked,date_completed)- (select count(*) from holidays where fulldate between date_booked and date_completed

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-11-11 : 10:52:16

so your holiday table includes date for week ends ?

KH
[spoiler]Time is always against us[/spoiler]

rwaldron
Posting Yak Master

131 Posts

Posted - 2009-11-11 : 11:35:17

Hiya,
No the holidays table doesn't contain weekends
but the function functwordays
calculates the weekdays between date_received and date_completed and I then subtract from this the count of holidays from the holiday table WITHIN THIS PERIOD...

Does this look ok ?

Ray..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources