dwain.c (9/26/2012)
Nice set up data Sean!Using it, I'd like to offer a slightly less verbose solution:
SELECT *, WaitTime=DATEDIFF(minute
,CAST(check_in AS TIME)
,CAST(STUFF(appt_time, 3, 0, ':') AS TIME))
FROM #CheckIn
The STUFF apparently forces the datatype to VARCHAR so minutes aren't lost.
Both solutions return negative minutes, so you may want to take that into account.
I think it needs one (semi)slight adjustment:
SELECT *, WaitTime=DATEDIFF(minute
,CAST(check_in AS TIME)
,CAST(STUFF(RIGHT('000' + CAST(appt_time AS varchar(4)), 4), 3, 0, ':') AS TIME))
FROM #CheckIn
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.