Tracking data changes per table column in T-SQL

Question

Given the table below I want to get the history of changes for the two columns price and qty for each value of fruit. I'm using MS SQL Server 2012.

So for example:

• the change between row 1 and 3 was that the price of apples went from 10 to 30 which gives row 1 in the result table below.
• the change between row 2 and 4 was that the price of bananas went from 20 to 30 and thw quantity went from 2 to 3 which gives rows 2 and 3 in the result table.

Is there a way to do this? Is there also a way to do this efficiently?

Source table

+----+---------+--------+--------+---------+
| id | fruit   | price  | qty    | created |
+----+---------+--------+--------+---------+
| 1  | apples  | 10     | 1      | 1/1/16  |
+----+---------+--------+--------+---------+
| 2  | bananas | 20     | 2      | 1/1/16  |
+----+---------+--------+--------+---------+
| 3  | apples  | 30     | 1      | 2/1/16  |
+----+---------+--------+--------+---------+
| 4  | bananas | 30     | 3      | 2/1/16  |
+----+---------+--------+--------+---------+
| 5  | apples  | 30     | 2      | 3/1/16  |
+----+---------+--------+--------+---------+
| 6  | apples  | 30     | 3      | 7/1/16  |
+----+---------+--------+--------+---------+

Results table

+----+----+--------+--------+--------+---------+
| id | fk | col    | oldval | newval | changed |
+----+----+--------+--------+--------+---------+
| 1  | 3  | price  | 10     | 30     | 2/1/16  |
+----+----+--------+--------+--------+---------+
| 2  | 4  | price  | 20     | 30     | 2/1/16  |
+----+----+--------+--------+--------+---------+
| 3  | 4  | qty    | 2      | 3      | 2/1/16  |
+----+----+--------+--------+--------+---------+
| 4  | 5  | qty    | 1      | 2      | 3/1/16  |
+----+----+--------+--------+--------+---------+
| 5  | 6  | qty    | 2      | 3      | 7/1/16  |
+----+----+--------+--------+--------+---------+

Answer 1

Preconditions for the following answer are that price and qty have the same datatype and id is an IDENTITY column.

The first step is to find changes. You can do that by numering all records of the same fruit ordered by it's id and then join subsequent records. Then you can UNPIVOT the result and filter unchanged colums out.

WITH
   SourceNumbered AS
      (
         SELECT
               ROW_NUMBER() OVER(PARTITION BY fruit ORDER BY id) AS nr,
               id, fruit, price, qty, created
            FROM
               SourceTable
      ),
   SourceUnpivoted AS
      (
         SELECT
               U.id, U.fk, U.col
            FROM
               (
                  SELECT
                        L.id, R.id AS fk,
                        L.price - R.price AS price,
                        L.qty - R.qty AS qty
                     FROM
                        SourceNumbered L
                        INNER JOIN SourceNumbered R
                           ON R.fruit = L.fruit
                              AND R.nr = L.nr + 1
               ) D
            UNPIVOT (value FOR col IN (price, qty)) U
            WHERE
               value != 0
      )
SELECT
      U.id, U.fk, U.col,
      CASE U.col
         WHEN 'price'
            THEN O.price
         WHEN 'qty'
            THEN O.qty
      END AS oldval,
      CASE U.col
         WHEN 'price'
            THEN N.price
         WHEN 'qty'
            THEN N.qty
      END AS oldval,
      N.created AS changed
   FROM
      SourceUnpivoted U
      INNER JOIN SourceTable O
         ON O.id = U.id
      INNER JOIN SourceTable N
         ON N.id = U.fk;

Since you can't unpivot more than one column, the case in the final SELECT is unavoidable.

Answer 2

Here's one way:

;WITH LagCTE AS (
  SELECT  id, fruit, price, qty, created,
          LAG(price) OVER (PARTITION BY fruit 
                           ORDER BY created) AS prevPrice,
          LAG(qty) OVER (PARTITION BY fruit 
                         ORDER BY created) AS prevQty
  FROM mytable
)
SELECT ROW_NUMBER() OVER (ORDER BY changed) AS id,
       fk, col, oldval, newval, changed
FROM (       
  SELECT id AS fk, fruit, 'price' AS col, 
         prevPrice AS oldval, price AS newval, 
         created AS changed
  FROM LagCTE
  WHERE prevPrice <> price

  UNION ALL 

  SELECT id AS fk, fruit, 'qty' AS col, 
         prevQty AS oldval, qty AS newval, 
         created AS changed
  FROM LagCTE
  WHERE prevQty <> qty) AS t

Demo here