Thanks much for the feedback and for writing!
Regarding your solution, there's no real need for row numbers to uniquely identify rows; you can rely on the id column....
With the following sample data:
TRUNCATE TABLE dbo.Transactions;
INSERT INTO dbo.Transactions(txid, val)
Martin, here you go:
SELECT keycol, from_ts, to_ts, sgn, y, m, d,
s / 3600 AS h,
s % 3600 / 60 AS mi,
s % 60 AS s,
(1000 + DATEPART(ms, to_ts) -...
This solution is absolutely brilliant!
Here are the stats that I got on my system (compared with two other solutions):
Itzik Double Negative: CPU time = 3875 ms,...
Your last solution is really fast! It runs for about 1 second on my system. The full stats are:
Dwain Variables: CPU time = 2891 ms, elapsed time = 900 ms., logical reads...