rickythefox
rickythefox

Reputation: 6861

Tracking data changes per table column in T-SQL

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:

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  |
+----+----+--------+--------+--------+---------+

Upvotes: 1

Views: 87

Answers (2)

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.

Upvotes: 1

Giorgos Betsos
Giorgos Betsos

Reputation: 72205

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

Upvotes: 4

Related Questions