Problem in Finding Hours Duration Between two Dates

[Vishnu Narayan Mishra]

Hi to All Forum members I am stuck in finding duration and Net duration in the following procedure I also adding image Url for better under standing of my requirement. Please go to this Url .. http://picasaweb.google.com/lh/photo/203UnHt9l8EnouLOtIKxBg?authkey=Gv1sRgCM6puf-sutfJ2QE&feat=directlink[^] Following is my T-Sql Query... Select F.Id as [FleetID], Convert(varchar(30), F.AssignedOn, 113) as [Date], D.DepotName as [Depot], DT.DutyID as [Duty Number], IsNull(Convert(varchar(30), F.ActualOutSheddingTime, 113),'Not Assigned') as [Actual OutShedding Time], IsNull(Convert(varchar(30), F.ActualInsheddingTime, 113) ,'Not Assigned') as [Actual Inshedding Time], IsNull(CONVERT(varchar(5), DATEADD(ms, DATEDIFF(ms, F.ActualOutSheddingTime, F.ActualInsheddingTime), 0), 114) ,'00:00')as [Duration], IsNull(DT.RestHours, 00) as [ShiftChangeoverTime], IsNull(CONVERT(varchar(5), DATEADD(ms, DATEDIFF(ms, CONVERT(varchar(5), DATEADD(ms, DATEDIFF(ms, F.ActualOutSheddingTime, F.ActualInsheddingTime), 0), 114), Convert(Datetime, '00:'+ IsNull(Convert(varchar(30), DT.RestHours, 114), 30))), 0), 114),'00:00') as [Net Duration] From Fleet F Left Outer Join Duties DT on F.DutyID = DT.ID Inner Join Depot D on DT.DepotId = D.ID Inner Join Vehicles V on V.ID = F.VehicleID Welcome for your valuable suggestions. Vishnu

Vishnu Narayan Mishra Software Engineer

[Vishnu Narayan Mishra]

Hi Actually Duration is coming right in this query and the issue is Net Duration is not coming right.

Vishnu Narayan Mishra Software Engineer