Problem in Finding Hours Duration Between two Dates in Sql Server 2005

See more:

SQL-Server-2005

SQL-Server

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