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.
| Author |
Topic |
|
chapm4
Yak Posting Veteran
58 Posts |
 Posted - 2012-04-03 : 10:43:23
|
I am trying to find the number of hours worked year to date for both hourly and salaried workers.SELECT SUM(hsmTotalHoursWorkedByHourlyEmp + hsmTotalHoursWorkedBySalariedEmp) AS TOTAL_HOURS_YTDFROM vw_hsmdHours2012 This gives me total hours in this case for January, February and March. I need to add avg hours worked for days of the current month (in this case 2). I set up both calendar tables and number table but not quite sure how to use them. Was thinking that I could take the sum of all hours worked for the past 12 months (based on today's date and 12 months back to do a rolling 12 months)divide by 12 and then divide by the number of days that have occured in the current month, but have no idea how to get there. Cry for help. |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2012-04-03 : 13:32:23
|
| [CODE]select avg(hsmTotalHoursWorkedByHourlyEmp + hsmTotalHoursWorkedBySalariedEmp) AvgCurrentMonthfrom vw_hsmdHours2012where MyDate >= DateAdd(month, DateDiff(month, 0, GetDate()), 0) -- Start of month and MyDate < DateAdd(day, DateDiff(day, 0, GetDate()), 0) -- Start of today[/CODE]=================================================There is a foolish corner in the brain of the wisest man. -Aristotle, philosopher (384-322 BCE) |
 |
|
|
chapm4
Yak Posting Veteran
58 Posts |
Posted - 2012-04-03 : 15:17:06
|
| What does myDate represent? I know I am dense. |
|
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2012-04-03 : 16:37:29
|
| I was assuming that somewhere in the table there is a date field that represents the date the work was done on but I have no idea what the column name is so I just used "MyDate". Substitute your actual column name and proceed.=================================================There is a foolish corner in the brain of the wisest man. -Aristotle, philosopher (384-322 BCE) |
|
|
|
|
|
|
|
|
avg hours worked in current month