query to identify changes - 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)

query to identify changes

Author

Topic

sebastian11c
Posting Yak Master

129 Posts

Posted - 2012-05-07 : 12:04:10

hi there i have a table called "changes" with this info

idchangeIDORDER office client datechange
1 1 miami paul 2012-05-07 10:20:12.050
2 1 new york john 2012-05-07 10:21:23.653
3 1 phoenix john 2012-05-07 10:23:14.340
4 1 los angeles thomas 2012-05-07 10:50:22.977
5 1 los angeles maria 2012-05-07 10:50:42.583

and i need a query that tells me what happend with the columns of that record ( in this case the record with idorder=1)

for example i need a query to compare the records with IDORDER=1 with the record inmediatly before with IDORDER=1

it could be the idchange=5 (with idorder=1) compare with the inmediatly record before where idorder=1 in this case the record with idchange=4, also compare idchange =4 with idchange =3, and etc

and ill get something like this

idchange idorder office client
5 1 not changed changed
4 1 changed changed
3 1 changed NOT changed
2 1 changed changed

any idea will be appreciate

many many thanks in advanced

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-07 : 14:20:46

You can join on the same table and do a select like this:

SELECT
	a.idchange,
	a.IDOrder,
	CASE WHEN a.Office = b.Office THEN 'NOT CHANGED' ELSE 'CHANGED' END AS OfficeChanged,
	CASE WHEN a.client = b.client THEN 'NOT CHANGED' ELSE 'CHANGED' END AS ClientChanged
FROM
	changes a 
	INNER JOIN changes b ON a.IdORDER = b.IdOrder AND a.IdChange = b.IdChange + 1
ORDER BY
	a.IdChange DESC

sebastian11c
Posting Yak Master

129 Posts

Posted - 2012-05-07 : 15:05:01

thanks for your reply

but i used a table for the example, but not always happens

a.IdChange = b.IdChange + 1

i have to find the idchange of the inmediatly next record where idorder=1,, not always will be +1

thanks in advanced

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-07 : 15:12:07

You can construct a sequential row number via a CTE and then use that row number to join on, like this:

;WITH cte AS
( 
	SELECT *, ROW_NUMBER() OVER (PARTITION BY IDOrder ORDER BY idChange) AS RN
	FROM changes
)
SELECT
	a.idchange,
	a.IDOrder,
	CASE WHEN a.Office = b.Office THEN 'NOT CHANGED' ELSE 'CHANGED' END AS OfficeChanged,
	CASE WHEN a.client = b.client THEN 'NOT CHANGED' ELSE 'CHANGED' END AS ClientChanged
FROM
	cte a 
	INNER JOIN cte b ON a.IdORDER = b.IdOrder AND a.RN = b.RN + 1
ORDER BY
	a.IdChange DESC

sebastian11c
Posting Yak Master

129 Posts

Posted - 2012-05-07 : 16:38:26

WHAT A CODE!!!!!
awesome

thanks many thanks
sunitabeck

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-07 : 17:09:07

sounds like change data capture implementation for interested columns to me

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

sebastian11c
Posting Yak Master

129 Posts

Posted - 2012-05-08 : 09:12:40

change data capture would be the right option but i have sql standard sdition, and i dont have the option;

it is possible to configure cdc on sql std edition?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-08 : 12:26:55

see below link on how to simulate it for standard edition.
in your case, if its just for two or three columns then its not worth using this

http://standardeditioncdc.codeplex.com/

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

sebastian11c
Posting Yak Master

129 Posts

Posted - 2012-05-08 : 12:48:10

have you used this app?
does it work?
is easy to manage and install?

thanks in advanced

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources