Determine which columns were changed in the rows of the table

Question

I have a SQL Server table that contains an incremental Measurement several different Attribute related to different Object s:

 MeasurementID ObjectID Attribute1 Attribute2 Attribute3 100 1 'blue' 111 'large' 101 1 'blue' 114 'large' 102 1 'red' 114 'large' 103 1 'red' 117 'large' 104 1 'cyan' 118 'large' 105 2 'blue' 450 'huge' 106 3 'blue' 450 'huge' 107 3 'red' 450 'huge' 

My goal is to find an efficient query that, given a specific ObjectID , tells me which attributes changed and when. For example, say ObjectID=1 . Attribute1 changed to MeasurementID=102 (from 'blue' to 'red' ), and similarly for Attribute2 in MeasurementID=101 and MeasurementID=103 , and for Attribute3 at any time ( NULL ). Conceptually, I'm looking for something that fulfills this pseudo-code definition:

 CREATE FUNCTION GetMeasurementChanges (ObjectID int, AttributeName varchar) RETURNS @returnMeasurementIDs TABLE (MeasurementID int) -- rows of MeasurementIDs 

I can think of a slow way to do this with poor performance by selecting a temp based on Object -by- Object in the temp table and then repeating the rows, checking each Attribute , but I suspect it will be very bad.

Has anyone got a trick or similar question that they can point to?

Thanks!

Answer 1

 CREATE FUNCTION dbo.GetMeasurementChanges -- always use schema prefix ( @ObjectID INT, @AttributeName VARCHAR(32) ) RETURNS TABLE -- use an inline table-valued function when possible WITH SCHEMABINDING AS RETURN ( WITH x AS ( SELECT ObjectID, MeasurementID, Attribute1, Attribute2, Attribute3, rn = ROW_NUMBER() OVER (ORDER BY MeasurementID) FROM dbo.MyTable -- update this of course WHERE ObjectID = @ObjectID ), y AS ( SELECT MeasurementID, r1 = CASE @AttributeName WHEN 'Attribute1' THEN ROW_NUMBER() OVER (PARTITION BY Attribute1 ORDER BY MeasurementID) END, r2 = CASE @AttributeName WHEN 'Attribute2' THEN ROW_NUMBER() OVER (PARTITION BY Attribute2 ORDER BY MeasurementID) END, r3 = CASE @AttributeName WHEN 'Attribute3' THEN ROW_NUMBER() OVER (PARTITION BY Attribute3 ORDER BY MeasurementID) END FROM x ) SELECT MeasurementID FROM y WHERE 1 IN (r1, r2, r3) AND NOT EXISTS ( SELECT 1 FROM x WHERE x.rn = 1 AND MeasurementID = y.MeasurementID ) ); GO 

Alternative:

 CREATE FUNCTION dbo.GetMeasurementChanges -- always use schema prefix ( @ObjectID INT, @AttributeName VARCHAR(32) ) RETURNS TABLE -- use an inline table-valued function when possible WITH SCHEMABINDING AS RETURN ( WITH x AS ( SELECT ObjectID, MeasurementID, Attribute1, Attribute2, Attribute3, rn = ROW_NUMBER() OVER (ORDER BY MeasurementID) FROM dbo.MyTable WHERE ObjectID = @ObjectID ), y AS ( SELECT MeasurementID, r = ROW_NUMBER() OVER ( PARTITION BY CASE @AttributeName WHEN 'Attribute1' THEN Attribute1 WHEN 'Attribute2' THEN CONVERT(VARCHAR(32), Attribute2) WHEN 'Attribute3' THEN Attribute3 END ORDER BY MeasurementID) FROM x ) SELECT MeasurementID FROM y WHERE r = 1 AND NOT EXISTS ( SELECT 1 FROM x WHERE x.rn = 1 AND MeasurementID = y.MeasurementID ) ); GO 

Answer 2

try it

 if (@attributeName = 'Attribute1') begin insert into @returnMeasurementIDs select measurementid from ( select measurementid, rank() over (partition by objectId, attribute1 order by measurementid) rnk, row_number() over (order by measurementid) rn from table where objectid = @ObjectID ) v where rnk = 1 and rn <> 1 end else if (@attributeName = 'Attribute2') begin insert into @returnMeasurementIDs select measurementid from ( select measurementid, rank() over (partition by objectId, attribute2 order by measurementid) rnk, row_number() over (order by measurementid) rn from table where objectid = @ObjectID ) v where rnk = 1 and rn <> 1 end else if (@attributeName = 'Attribute3') begin insert into @returnMeasurementIDs select measurementid from ( select measurementid, rank() over (partition by objectId, attribute3 order by measurementid) rnk, row_number() over (order by measurementid) rn from table where objectid = @ObjectID ) v where rnk = 1 and rn <> 1 end 

SQL DEMO

Rank - used to rank each group by object and attribute, and it will return 1 when the attribute changes.

Row_Number is used to rank all rows and ignore the first row from the list of all changed dimension identifiers so that it returns rows 102, 104, etc.

Answer 3

try it

 SELECT CASE WHEN a.Attribute1 = b.Attribute1 THEN 1 END AS Attribute1, CASE WHEN a.Attribute2 = b.Attribute2 THEN 1 END AS Attribute2, CASE WHEN a.Attribute3 = b.Attribute3 THEN 1 END AS Attribute3 FROM Measurements a INNER JOIN Measurements b ON (b.MeasurementID = (SELECT MAX(MeasurementID) FROM Measurements c WHERE c.MeasurementID < a.MeasurementID AND c.ObjectID = a.ObjectID)) 

Answer 4

Use parameter with TOP 1 + CROSS APPLY statement

 CREATE FUNCTION dbo.getMeasurementChanges(@ObjectID int, @AttributeName nvarchar(10)) RETURNS TABLE WITH SCHEMABINDING AS RETURN ( SELECT o.MeasurementID FROM dbo.test90 t CROSS APPLY ( SELECT TOP 1 MeasurementID, ObjectID, CASE @AttributeName WHEN 'Attribute1' THEN t.Attribute1 WHEN 'Attribute2' THEN CAST(t.Attribute2 AS nvarchar(10)) WHEN 'Attribute3' THEN t.Attribute3 END AS t1Attributes, CASE @AttributeName WHEN 'Attribute1' THEN t2.Attribute1 WHEN 'Attribute2' THEN CAST(t2.Attribute2 AS nvarchar(10)) WHEN 'Attribute3' THEN t2.Attribute3 END AS t2Attributes FROM dbo.test90 t2 WHERE t2.ObjectID = @ObjectID AND t.MeasurementID < t2.MeasurementID ORDER BY MeasurementID ASC ) o WHERE t.ObjectID = @ObjectID AND t1Attributes != t2Attributes) 

To improve performance, use the following indexes:

 CREATE INDEX x ON dbo.test90(ObjectID) INCLUDE(MeasurementID, Attribute1, Attribute2, Attribute3) CREATE INDEX x2 ON dbo.test90(MeasurementID) INCLUDE(ObjectID, Attribute1, Attribute2, Attribute3) 

SQLFiddle Demo