Reputation: 6861
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
Reputation: 3515
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
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
Upvotes: 4