For complex topics that regular users would not be interested in. For power users and database administrators.
-
KevinRossen
- Posts: 293
- Joined: Mon Apr 22, 2013 8:49 am
- Location: Dallas, TX
-
Contact:
Post
by KevinRossen » Tue Jan 28, 2014 9:39 am
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
- Posts: 1553
- Joined: Tue Nov 30, 2010 12:33 pm
-
Contact:
Post
by jsalmon » Tue Jan 28, 2014 10:24 am
My initial guess is that your query using SUM(HOUR... it's probably just not considering MINUTES.
The best thing about a boolean is even if you are wrong, you are only off by a bit.
Jason Salmon
Open Dental Software
http://www.opendental.com
-
KevinRossen
- Posts: 293
- Joined: Mon Apr 22, 2013 8:49 am
- Location: Dallas, TX
-
Contact:
Post
by KevinRossen » Tue Jan 28, 2014 10:43 am
jsalmon wrote: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