Help in SQL Query - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

Help in SQL Query

Author

Topic

cidmi.dovic
Yak Posting Veteran

53 Posts

Posted - 2009-06-11 : 07:54:32

Hi,
I want to know if it's possible to make a query for this:

My table has an id[int] and a DateTime Field

1 2009-01-01 00:00:00
2 2009-01-01 12:00:00
3 2009-01-01 13:50:14

It there any way to make a query for take the datediff between two records, the current and the prior. I'm thinking on using cursors but maybe there's a better option.

Thanks in advance.

The Padrón peppers itch and other don't

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-06-11 : 08:00:51

the Id controls which is before and after? If so then yes you can do this


DECLARE @foo TABLE (
	[ID] INT
	, [val] DATETIME
	)

INSERT @foo ([ID], [val])
      SELECT 1, '2009-01-01 00:00:00'
UNION SELECT 2, '2009-01-01 12:00:00'
UNION SELECT 3, '2009-01-01 13:50:14'

SELECT
	a.[ID]
	, a.[val] AS [Record Timestamp]
	, b.[val] AS [Prev Record Timestamp]
	, ABS(DATEDIFF(SECOND, a.[val], b.[val])) AS [Difference (Seconds)]
FROM
	@foo a
	LEFT JOIN @foo b ON b.[Id] = a.[Id] - 1

If the ID sequence has gaps in it you can use a Common table expression with a ROW_NUMBER() column to get an unbroken sequence.

All the best.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

cidmi.dovic
Yak Posting Veteran

53 Posts

Posted - 2009-06-11 : 09:44:23

Thanks a lot.

The Padrón peppers itch and other don't

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources