Calculate time since prior record

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Calculate time since prior record

Author

Topic

jmersing
Yak Posting Veteran

57 Posts

Posted - 2011-05-04 : 08:54:36

select CONVERT(char(8), DATEADD(second, TRLogTime, '')-DateAdd(Hour,5,114)
, 114) as LogTime,

FROM (MyTable

order by UserDispName, LogTime, Inv asc

I would like to be able to calculate the duration from the prior record. The query returns a name, time and then I sort it, is there a way to refer to the prior record for the purpose of performing a calculation?

Thanks

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-04 : 09:03:34

Not without the tables be ordered so that prior means something. After that you'll probably have to join the the table to itself to get the prior value.

Jim

Everyday I learn something that somebody else already knew

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-05-04 : 09:40:42

CREATE TABLE #MyTable (TRLogTime DATETIME)
INSERT INTO #MyTable
SELECT '2011-05-04 09:12:43'
UNION ALL
SELECT '2011-05-04 09:43:54'
UNION ALL
SELECT '2011-05-04 10:13:13'

SELECT ISNULL(TRLogTime
- ( SELECT TOP ( 1 )
TRLogTime
FROM #MyTable
WHERE TRLogTime < OuterTable.TRLogTime
ORDER BY TRLogTime DESC
),'00:00:00') AS BatchPerTenMin
FROM #MyTable OuterTable
ORDER BY TRLogTime

DROP TABLE #MyTable

--------------------------
http://connectsql.blogspot.com/

jmersing
Yak Posting Veteran

57 Posts

Posted - 2011-05-04 : 10:07:43

Thanks guys Ill give it a try

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources