>Hi all
>
>I would have attached a screen shot of the sample data but I guess that is not
>possible. What I am trying to achieve right now is get the time difference in
>hh:mm format to the user
>
>tStartTime and tEndTime field values
>
>26.08.2014, 08:38:00.000 26.08.2014, 09:45:00.000
>26.08.2014, 10:00:00.000 26.08.2014, 10:45:00.000
>26.08.2014, 11:00:00.000 26.08.2014, 12:00:00.000
>26.08.2014, 11:30:00.000 26.08.2014, 12:15:00.000
>26.08.2014, 00:15:00.000 26.08.2014, 13:30:00.000
>
>and the following is the query
>
>SELECT sProdLineWorkProcess.*, (DATEDIFF(HOUR,
>sProdLineWorkProcess.tStartTime, sProdLineWorkProcess.tEndTime)) AS
>nHoursWorked
> , (DATEDIFF(MINUTE, sProdLineWorkProcess.tStartTime,
> sProdLineWorkProcess.tEndTime) - (DATEDIFF(HOUR,
> sProdLineWorkProcess.tStartTime, sProdLineWorkProcess.tEndTime) * 60)) AS
> >nHoursWorked
> , (DATEDIFF(MINUTE, sProdLineWorkProcess.tStartTime,
> sProdLineWorkProcess.tEndTime)) AS nTotalMinutesWorked
> FROM sProdLineWorkProcess
> JOIN tProdLineWork
> ON tProdLineWork.iID = sProdLineWorkProcess.iPID
> WHERE CAST(tProdLineWork.tDt AS DATE) = '2014-08-26'
> --GROUP BY sProdLineWorkProcess.iEmployeeID
>
>The calculated field values are as follows:
>
>1 7 67
>0 45 45
>1 0 60
>1 -15 45
>13 15 795
>
>Please see the 4th row, the diff between 11:30 and 12:15 is 1 Hour and -15
>mins whereas difference between 10:00 and 10:45 is 0 hour and 45 mins.
>
>Please advise on what I need to do correctly to get 0 hour and 45 mins for
>both 2nd and 4th row.
If you only care about hours and minutes, then your attempt is pretty close to
one possible solution, Bhavbhuti. Try something similar to (assuming
sProdLineWorkProcess has a unique ID field):
with tmp(ID, nHours, nMinutes) as
(SELECT plwp.ID, DATEDIFF(HOUR, plwp.tStartTime, plwp.tEndTime),
DATEDIFF(MINUTE, plwp.tStartTime, plwp.tEndTime) - (DATEDIFF(HOUR,
plwp.tStartTime, plwp.tEndTime) * 60)
FROM sProdLineWorkProcess plwp
JOIN tProdLineWork plw
ON plw.iID = plwp.iPID
WHERE CAST(tProdLineWork.tDt AS DATE) = '2014-08-26')
select plwp.*, iif(nMinutes < 0, nHours - 1, nHours) nHoursWorked,
iif(nMinutes < 0, nMinutes + 60, nMinutes) nMinutesWorked
from tmp
join sProdLineWorkProcess plwp on tmp.ID = plwp.ID
HTH,
Set
