Getting the current state from a "log" table

Question

Getting the current state from a "log" table

RiNSpy

SSC Rookie

Points: 33
More actions
January 29, 2007 at 3:22 pm

#92193

Hi,
I have a db table here, that's used to log the changes of states of some objects. It's fields are "objectId", "eventDateTime" and "newState". Every time an object's state is changed, a new row is added to the table stating the object's id, the datetime of the event and the newstate (e.g. either 1, 2 or 3).
Now, the question is, what is the best way of getting the ID's of all the object's that are currently being in a particular state?
All I can think of right now is to make a "select" query for each objectId separately, order it by eventDatetime and check the top row's "newState" to get it's current state.
Surely there should be a better way?
Thank you.
Cheers,
Victor

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

The current state of any object will be in the system tables. You should not need to cross reference your 'log' table. By the way, are you running SQL Server 2000 or 2005? If you are running 2000, I assume that you've added triggers to some of the system tables to get your log data to populate? If so, I should warn you that altering system tables will void your Microsoft support. If you are using 2005's DDL triggers or you've come up with another method without altering system tables, great. Also, from a auditing/logging perspective, wouldn't you want your 'log' table to hold the previous state? It may help if you describe in more detail what you are trying to do as I have a feeling we're not getting the whole picture here. Can you post your 'log' table's DDL and walk us through an example?

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

RiNSpy SSC Rookie Points: 33 More actions · Answer 2

Thank you for your reply John.

Those "objects" are from a third-party application, which populates the table. Here's an example of the table:

objectID eventDatetime newState

82 30/01/2007 13:16:16 3

83 30/01/2007 13:16:13 2

83 30/01/2007 13:10:14 3

81 30/01/2007 13:04:32 2

82 30/01/2007 13:01:10 2

81 30/01/2007 13:00:00 1

Now, if I wanted to get the ID's of all the objects currently being in state "2", I would get "83" and "81" as a result.

The question is, what is the best way to query the database to get this result?

Hope that clarified the question.

John Rowan SSC Guru Points: 56440 More actions · Answer 3

OK, so I misunderstood and you're not working off of the system tables. If I am understanding you now, you only want to query this single table that you've shown. This should do it for you:

DECLARE @TableVariable TABLE (ObjectID int, EventDateTime datetime, NewState int)

INSERT INTO @TableVariable (objectID, eventDatetime, newState)

SELECT 82, '2007-01-30 13:16:16', 3 UNION ALL

SELECT 83, '2007-01-30 13:16:13', 2 UNION ALL

SELECT 83, '2007-01-30 13:10:14', 3 UNION ALL

SELECT 81, '2007-01-30 13:04:32', 2 UNION ALL

SELECT 82, '2007-01-30 13:01:10', 2 UNION ALL

SELECT 81, '2007-01-30 13:00:00', 1

SELECT t1.*

FROM @TableVariable t1

INNER JOIN (SELECT ObjectID, MAX(EventDateTime) AS NewestDate

FROM @TableVariable

GROUP BY ObjectID

) t2

ON t1.ObjectID = t2.ObjectID AND t1.EventDateTime = t2.NewestDate

WHERE t1.NewState = 2

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

RiNSpy SSC Rookie Points: 33 More actions · Answer 4

That's exactly what I needed, thank you very much indeed!