Using data from the next row - 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 2005 Forums

Transact-SQL (2005)

Using data from the next row

Author

Topic

porksmash
Starting Member

2 Posts

Posted - 2007-10-26 : 13:44:34

Hiya,

I have a table that stores records of what time this machine changes modes, and what mode it changed into. Basically a datetime column and an int column. What I need to do is figure out the time difference in minutes between two sequential rows. Oracle has LEAP and LAG functions that do exactly what I need, but there doesn't seem to be any equivalent in MS-SQL. This would also be pretty easy if I could do some programming outside the queries, but unfortunately the app I'm writing the query for doesn't have any date/time abilities, and I can't custom code it. SQL queries only.

Sample table:


occured                      machine_state
----------------------       -------------
10/10/2007 12:00:00 AM	     4
10/10/2007 12:04:00 AM	     0
10/10/2007 12:05:00 AM	     1
10/10/2007 12:27:00 AM	     3
10/10/2007 12:32:00 AM	     0
10/10/2007 12:33:00 AM	     2

So basically, for the first two rows, I need to get the number 4 out of both of those dates. The biggest problem is getting the info from both rows to do a datediff on em.

Another gotcha is if it is the last row, I need to check the time difference between the row and NOW().

-Pete

Edit: Using SQL 2005 if it matters.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-10-26 : 14:10:34

You could use the RowNumber() option and join to a derived table, something like this perhaps that would put the consecutive evens in the same row...

Select [datetimecolumn],LastDateTime,LastState state,RowNumber() as RowNum
From [Table] left join
(Select [datetimecolumn] as LastDatetime, state as laststate,RowNumber() as lastRowNum
From [Table]
Order by [Datetimecolumn] asc) LastTable
on [Table].RowNum = (LastTable.lastRowNum + 1)

Will H
Yak Posting Veteran

56 Posts

Posted - 2007-10-26 : 14:37:34

You'll have to change StateID to your field name and the table name 'State' to your table name, but here is an example. You could also implement a UDF, but I feel dirty withusing those.

The COALESCE() call is just to return the current time when the next occuring isn't found.

SELECT oldState
, (SELECT StateID FROM State WHERE State.occured = results.Changed) NewState
, Occured
, Changed
, DateDiff(mi, Occured, Changed) MinutesDifference
FROM (
SELECT StateID oldState, Occured,
COALESCE ( (SELECT MIN(Occured) FROM State r WHERE r.Occured > p.Occured) , GETDATE() ) Changed
FROM State p
) results
ORDER BY Occured

---------------------------------------------------------
SSRS Kills Kittens.

porksmash
Starting Member

2 Posts

Posted - 2007-10-26 : 14:56:05

Thanks, I was able to get what I wanted with your help.

IRBradles
Starting Member

5 Posts

Posted - 2007-10-30 : 20:53:37

Copied and renamed fields from current Database I am building. (Horrible Hybrid of Access Front End/SQLSrv 2005 Backend)
Hope it Helps.

SELECT T1.Occured, DateDiff(mi,T2.Occured,T1.Occured), T1.Machine State, T2.Machine State
FROM Sample T1, Sample T2
WHERE T2.Occured = (SELECT MAX(T2.Occured) FROM Sample WHERE T2.Occured<T1.Occured)
OR (T1.Occured = T2.Occured AND T1.Occured = (SELECT MIN(Occured) FROM Sample);

Final 'Or' Clause Should pick up the initial Entry

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources