问题描述:

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

--EDIT--

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