Time Elapsed (1 Viewer)

[Angelflower]

Hello... I am trying to get have a column that will compare two dates (start date and Must Complete by Date) and come back with the time elapsed by hour and minute. I can get it give total minutes but I need that to be broke down to how many hours and what minutes are left. I've been racking my brain around it for a couple of days now. Hope someone out there can help me.

thanks in advance for your help!

SELECT dbo.workorder.wonum,
dbo.workorder.actstart,
(DateAdd("hh",24,dbo.workorder.reportdate) )AS [MustCompleteBy],
(getdate()) AS [Today],
dbo.workorder.actfinish,
( case when workorder.location like 'J%' then left(workorder.location,6)
else left(workorder.location,5) end ) As building,



--Completion Column
(CASE WHEN workorder.actfinish IS NULL and ((GETDATE()) <= (DateAdd("hh",24,dbo.workorder.reportdate))) Then
CASE WHEN (CONVERT(VARCHAR(23),(DATEDIFF("mi",GETDATE(),((DateAdd("hh",24,dbo.workorder.reportdate))))),101)) > 60
Then (CONVERT(VARCHAR(23),(DATEDIFF("hh",GETDATE(),((DateAdd("hh",24,dbo.workorder.reportdate))))),101)) + ' Hours and '
+ (CONVERT(VARCHAR(23),(DATEDIFF("mi",GETDATE(),((DateAdd("hh",24,dbo.workorder.reportdate))))),101))+ ' Minutes Left'
ELSE (CONVERT(VARCHAR(23),(DATEDIFF("mi",GETDATE(),((DateAdd("hh",24,dbo.workorder.reportdate))))),101)) +' Minutes Left'
END
ELSE 'Past Due'
END) AS [Completion],

--testing Cast statement
(CASE WHEN workorder.actfinish IS NULL and ((GETDATE()) <= (DateAdd("hh",24,dbo.workorder.reportdate))) Then
CAST(DATEDIFF(hh, GETDATE(),(DateAdd("hh",24,dbo.workorder.reportdate)) )AS VARCHAR) + ':' +
(CAST(DATEDIFF(mi, GETDATE(),(DateAdd("hh",24,dbo.workorder.reportdate))) AS VARCHAR) )
ELSE 'Past Due'
END) AS [Completion_new],

(CONVERT(VARCHAR(23),(DATEDIFF("hh",GETDATE(),((DateAdd("hh",23,dbo.workorder.reportdate))))),101)) as 'Hours Left',
((CAST(DATEDIFF(mi, GETDATE(),(DateAdd("hh",24,dbo.workorder.reportdate))) AS VARCHAR) )-60) * -1 as 'Minutes Left'

FROM {oj dbo.workorder LEFT OUTER JOIN dbo.assignment ON dbo.workorder.wonum = dbo.assignment.wonum AND dbo.workorder.orgid = dbo.assignment.orgid
LEFT OUTER JOIN dbo.labor ON dbo.assignment.laborcode = dbo.labor.laborcode AND dbo.assignment.orgid = dbo.labor.orgid }

 

[Angelflower]

Things always seem to come together frist thing in the morning. This is how I solved my problem for those intrested:

drop table ##TimeTotals

SELECT dbo.workorder.wonum,
dbo.workorder.reportdate,
convert(char(2),((CAST(DATEDIFF(mi, GETDATE(),(DateAdd("hh",24,dbo.workorder.reportdate))) AS VARCHAR) )/60) )as 'HoursLeft',
convert(char(2),((CAST(DATEDIFF(mi, GETDATE(),(DateAdd("hh",24,dbo.workorder.reportdate))) AS VARCHAR) )-(((CAST(DATEDIFF(mi, GETDATE(),(DateAdd("hh",24,dbo.workorder.reportdate))) AS VARCHAR) )/60)*60))) as 'MinutesLeft'

INTO ##TimeTotals

FROM {oj dbo.workorder LEFT OUTER JOIN dbo.assignment ON dbo.workorder.wonum = dbo.assignment.wonum AND dbo.workorder.orgid = dbo.assignment.orgid
LEFT OUTER JOIN dbo.labor ON dbo.assignment.laborcode = dbo.labor.laborcode AND dbo.assignment.orgid = dbo.labor.orgid }

/******************************************************************************************************************************/

SELECT dbo.workorder.wonum,
dbo.workorder.actstart,
(DateAdd("hh",24,dbo.workorder.reportdate) )AS [MustCompleteBy],
(getdate()) AS [Today],
dbo.workorder.actfinish,
( case when workorder.location like 'J%' then left(workorder.location,6)
else left(workorder.location,5) end ) As building,
##TimeTotals.HoursLeft,
##TimeTotals.MinutesLeft,

--Completion Column
(CASE WHEN workorder.actfinish IS NULL and ((GETDATE()) <= (DateAdd("hh",24,dbo.workorder.reportdate))) Then
CASE WHEN (CONVERT(VARCHAR(23),(DATEDIFF("mi",GETDATE(),((DateAdd("hh",24,dbo.workorder.reportdate))))),101)) > 60
Then ##TimeTotals.HoursLeft + ' Hours and ' + ##TimeTotals.MinutesLeft + ' Minutes Left'
ELSE (CONVERT(VARCHAR(23),(DATEDIFF("mi",GETDATE(),((DateAdd("hh",24,dbo.workorder.reportdate))))),101)) +' Minutes Left'
END
ELSE 'Past Due'
END) AS [Completion]


FROM {oj dbo.workorder LEFT OUTER JOIN dbo.assignment ON dbo.workorder.wonum = dbo.assignment.wonum AND dbo.workorder.orgid = dbo.assignment.orgid
LEFT OUTER JOIN dbo.labor ON dbo.assignment.laborcode = dbo.labor.laborcode AND dbo.assignment.orgid = dbo.labor.orgid
LEFT OUTER JOIN ##TimeTotals ON ##TimeTotals.wonum = dbo.workorder.wonum}