Selectively summing hours from a date range
Posted on 2011-10-12
I'm trying to use a sql query(s), perhaps along with a user defined function to find out how long a ' repair ticket' was held, given a date range and the fact that employees work from 8 to 5, and do not work on sundays or holidays. The holidays will come from a separate table--call it tblHolidays--listing specific dates, such as 2011-10-10 for columbus day. The Hours will come from another table, call it tblEmpInfo with date fields StartTime and QuitTime (8 am and 5 pm respectively) For Example: If you simply run
select datediff(hh, '2011-10-07 8 am', '2011-10-11 5 pm')
you come up with 105 hours. But you need to disregard sunday, october 9, and also columbus day on 2011-10-10. Plus, the days are only 9 hours. So what we want is 9 hours on the 7'th + 9 hours on the 8'th + 9 hours on the 11'th = 27 hours -- not 105 hours.