SQL to calculate time since last message?
This is a bit weird. I'm trying to use `DATEDIFF()` to calculate the time since the last message on an interface. For some reason, when no messages are received, the number steadily decreases. This is the opposite of what should happen. Here is my current query and a few sequential result sets:
SELECT getDate() as now, max(TimeCreated) as latest, DATEDIFF (s,getdate(), max(TimeCreated)) as difference FROM EnsLib_HL7.Message
now latest difference
------------------- ------------------- ----------
2016-05-13 11:05:31 2016-05-13 08:51:16 9945
2016-05-13 11:08:16 2016-05-13 08:51:16 9780
2016-05-13 11:08:36 2016-05-13 08:51:16 9760