Thanks a lot SET that worked as always.

Kind regards
Bhavbhuti

 

>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

-- 


Thanking you.

Yours Faithfully,
For Venus Software Operations
----
Mr. Bhavbhuti Nathwani
___________________________________________
Softwares for Indian Businesses at: http://www.venussoftop.com

venussoftop@...
venussoftop@...
___________________________________________

Please note: We reserve complete rights for policy changes in the future and the same will be applicable immediately as and when made.  Attachments may get corrupted before reaching you, in such a situation please let us know and we will resend you the same at the earliest.  We do not take any responsibility for data loss of any type and kind.  Data safety remains the sole the responsibility of the users of our softwares.
___________________________________________

Internet email confidentiality:

This message may contain information that may be privileged or confidential.  If you are not the addressee nor are you responsible for the delivery of the message to the addressee indicated in this email, then you may not copy or deliver this email to anyone and you should notify the sender by reply email and then destroy this message.

Please reply email immediately to this message with REMOVE in the subject, if you or your employer do not consent to email of this kind.

Opinions, conclusions and other information in this message that do not relate to the official business of my firm shall be understood as neither given nor endorsed by my company.