How to Calculate Working Hours including Minutes?

I have a query that fetches employee's name, date, working hours, wage and calculate salary based on working hours * wage.

开发者_StackOverflow社区

The minutes in working hours that is being calculated are discarded. I only get the value in full hour. Snapshot example:

My main concern is on workingHours and wageAmount

workingHours is displayed as 5. Here is the time sheet snapshot:

As you see shift starts at 10:30 AM and ends at 4:00 PM (excluding lunch time), so the actual total hours is 4 and a half not 5.

Here is the query snippet that calculates the hours:

   DATEDIFF(HOUR, shiftStart.timeEntered, shiftEnd.timeEntered) - DATEDIFF(HOUR, lunchStart.timeEntered, lunchEnd.timeEntered) AS workingHours, 

MY QUESTION IS:

1. How can i calculate working hours accurately with minutes included (discard seconds)?

Since i will be counting the minutes, i will have to calculate the wage per minute so i will have to put the following code that calculates wage per minute:

              ROUND((((DATEDIFF(HOUR, shiftStart.timeEntered, shiftEnd.timeEntered) - DATEDIFF(HOUR, lunchStart.timeEntered, lunchEnd.timeEntered)) * 60) * (cast(((w.wageAmount/60)-((w.wageAmount/60)%.001)) as decimal (18,3)))), 0) AS "SalaryPerMin",

---

Use DATEDIFF(minute, shiftStart.timeEntered, shiftEnd.timeEntered) * 1.0 / 60 instead, to give you the hour as a decimal.

---

Time card stuff is very business rule intensive. I would consider your architecture before putting all the rules in sp's.

---

This will return the Hours & Minutes: CAST(DATEDIFF(MINUTE, StartTime, EndTime) AS DECIMAL)/60