Calculating Hours Worked From Schedule

[KevinRossen]

I'm having an issue trying to calculate the number of hours worked for our providers. We don't use the timecard (our providers are paid salary), but I do have the schedule setup with individual providers' schedules. We stagger our lunches, so one provider may have a lunch break starting at say 12:20 while another one has it at 1:00. When I try to summarize the hours worked I'm not coming up with an accurate result.

Here's our schedule that I'm working with:


Here's the code I'm using:

Code: Select all

SET @FromDate = '2014-01-27', @ToDate  = '2014-01-27';
SELECT ProvNum, SUM(HOUR(TIMEDIFF(StopTime, StartTime))) AS 'Hours'
FROM schedule
WHERE SchedDate BETWEEN @FromDate AND @ToDate
		AND SchedType = 1 /* Provider */ 
GROUP BY ProvNum

And here's the result:


Any ideas???

[jsalmon]

My initial guess is that your query using SUM(HOUR... it's probably just not considering MINUTES.

[KevinRossen]

My initial guess is that your query using SUM(HOUR... it's probably just not considering MINUTES.

That's exactly what it was. I got the code from Query Example #640. Below is the code that I came up with that does what I need it to do, rounded to 1 decimal point (to account for half hours).

Code: Select all

SET @FromDate = '2014-01-27', @ToDate  = '2014-01-27';
SELECT ProvNum, ROUND(SUM(TIME_TO_SEC(TIMEDIFF(StopTime, StartTime)))/(60*60),1) AS 'Hours'
FROM SCHEDULE 
WHERE SchedDate BETWEEN @FromDate AND @ToDate
		AND SchedType = 1 /* Provider */ 
GROUP BY ProvNum