当前位置: 动力学知识库 > 问答 > 编程问答 >

tsql - SQL function? Required hours per month

问题描述:

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

分享给朋友:
您可能感兴趣的文章:
随机阅读: