Just a quick question,
Is there a function that calculates the required hours per month available in SQL?
If not a holiday counter?
I want to create a table in SQL which is a calendar that looks something like this:
Year Month Required_Hours
2011 1 *
2011 2 *
Where the function would calculate the * value
Required hours is: 8 hours per day
Days that are not counted: Saturday and Sunday + Holidays.
In MySQL you could do something like this SELECT 24 * DAYOFMONTH(LAST_DAY(CONCAT(Year,'-',Month,'-',1)))
It would return the days in a month times 24 which equals the number of hours in that particular month. Maybe there are some similar functions in TSQL?
Short answer: there's no built-in SQL function.
Long answer: this obviously depends on how many holidays there are during a particular month. One way to handle this is to have a table that stores general holidays and subtract those from the total. On top of that, you might need to take into account personal holidays / vacation days per employee, and that would be yet another table. However, once you have those, it's not that bad: use
DATEPART(weekday, *date*) to find out whether a date is a Saturday or Sunday