Determine which columns have changed in rows of a table

Question

I have a SQL Server table which contains incremental Measurements of several different Attributes pertaining to various Objects:

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 objective is to find an efficient query which, given a specific ObjectID tells me which attributes changed and when. For example, let's say ObjectID=1. Attribute1 changed at MeasurementID=102 (from 'blue' to 'red'), and similarly for Attribute2 at MeasurementID=101 and MeasurementID=103, and for Attribute3 at no time (NULL). Conceptually, I'm looking for something which 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 it with poor performance by selecting into a temp table on an Object-by-Object basis and then iterating through the rows, testing each Attribute, but I suspect that's going to perform very poorly.

Anyone got a trick or an analogous question they can point me to?

Thanks!

Answer 1

Use option with TOP 1 + CROSS APPLY operator

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)

For improving performance use this 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)

Demo on SQLFiddle

Answer 2

try this

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 3

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

An 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 4

Try this

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 - is used to rank each group by objectid and attribute and it will return 1 when attribute changes.

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