Number of times a value has been 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 2000 Forums

SQL Server Development (2000)

Number of times a value has been changed.

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-05-14 : 00:13:00

Matthew writes "I've got an audit table that is updated automatically using a trigger. Every time a field value is changed in a particular table, a row is added to the audit table to log a snapshot of the change history.

I want to write a query that will select all records in the audit table for which a particular field value has changed more than twice.

For example: A table exists with the following values:

RealTable
ID Name Address AccountBalance
10 Joe Shmo Any Address 1,115,154

If a user named MShare changes Joe Shmo's account balance 2 times via update statements, the following records would exist in the audit table.

AuditTable
ID Name Address AccountBalance ChangedBy
10 Joe Shmo Any Address 1,115,154 Original
10 Joe Shmo Any Address 2,076 Mshare
10 Joe Shmo Any Address 1,345 Mshare

I am trying to write a query that will give me all of the details of the audit records only for the accounts for which the account balance has been changed more than 2 times. Please help if you can. Thanks."

olily
Starting Member

37 Posts

Posted - 2002-05-14 : 00:45:59

You can try something like this:

SELECT * FROM AUDITTABLE WHERE (SELECT COUNT(ID) FROM AUDITTABLE) > 2

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2002-05-14 : 06:45:15

This should return the full details for any ID where the account balance has been changed more than 2 times.

SELECT *
FROM AuditTable
WHERE ID IN
(SELECT DISTINCT ID FROM
(SELECT ID
FROM AuditTable
GROUP BY ID, AccountBalance
HAVING COUNT(*) > 2) as tt)

Nazim
A custom title

1408 Posts

Posted - 2002-05-14 : 08:43:18

How about this one

select A.* from Audittable A
inner join (select id from Audittable
group by id
count(1)>1 ) t
on A.Id=t.Id

HTH

--------------------------------------------------------------

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources