Show the last date a value changed

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)

Show the last date a value changed

Author

Topic

ciag
Starting Member

2 Posts

Posted - 2012-07-11 : 05:07:02

Greetings folks,

I am trying to build a SELECT query but am stuck on the most important part of my desired query.

I have a table called 'InterfaceAvailability' which contains 4 columns
DateTime, InterfaceID, NodeID & Availability. I have been able to use the nodeID and interfaceID columns with INNER JOINS to gather other details from other tables for this report.
But the one big thing I am missing requires the use of the 'DateTime' and 'Availability' columns which is where my limited knowledge runs out.

At a high level, what I want the reprot to do is to tell me the last 'datetime' the 'availability' value changed. This should be possible as for every entry in the availability column there is a corresponding date-time stamp in the 'DateTime' column.

The aviliability values can be any from 1-100 so the query could be something along the lines of one of these two;

"The last datetime an availability value was different to the 'current' value"

OR

"The last DateTime 2 consecutive availability values were not identical"

Or maybe there is a much more simplistic way to do this. Please let me know if you need more info.

Thank you

Ciaran

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-07-11 : 05:25:11

select max([DateTime]) as [maxDateTime] from
(select
row_number() over (partition by availability order by [DateTime]) as rnum,* from InterfaceAvailability)dt
where rnum=1

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-11 : 11:02:43

[code]
SELECT MAX(t.DateTime) AS MaxDate
FROM table t
CROSS APPLY (SELECT TOP 1 Availability
FROM table
WHERE DateTime < t.DateTime
ORDER BY DateTime DESC)t1
WHERE t1.Availability <> t.Availability
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources