asked
How do I parse a price change historical table across many lines?
I am trying to build a table detailing historical price changes from a system extract that has generated an audit line every time a change has been made to any attribute relating to the product (not necessarily just price!). We don't have a nice SCD just this horrible audit table. The format of the table I am trying to create is as such: +--------+--------------+--------+------------+------------+ | RowKey |Product Code | Price | ValidFrom | ValidTo | +--------+--------------+--------+------------+------------+ | 1 | 10039997 | £0.00 | 2014-01-31 | 2015-03-04 | +--------+--------------+--------+------------+------------+ | 2 | 10039997 | £25.86 | 2015-03-05 | 2015-06-03 | +--------+--------------+--------+------------+------------+ | 3 | 10039997 | £62.69 | 2015-06-04 | 9999-12-31 | +--------+--------------+--------+------------+------------+ The source on the otherhand isn't so neat - below is the data that corresponds to the product above: Product Code Log Action Number Log Action Type Net Price 1 New Net Prices 1 New Retail Price Sales Unit New Sales Unit Cur Price Effective Price Effective From 10039997 177873 B 0 0 0 EA EA 31/01/2014 --/--/---- 10039997 177873 F 0 0 0 EA EA 31/01/2014 --/--/---- 10039997 283777 B 0 25.86 25.86 EA EA 31/01/2014 05/03/2015 10039997 283777 F 25.86 0 0 EA EA 05/03/2015 --/--/---- 10039997 283840 B 25.86 0 0 EA EA 05/03/2015 --/--/---- 10039997 283840 F 62.69 0 0 EA EA 05/03/2015 --/--/---- 10039997 353634 B 62.69 62.69 62.69 EA EA 05/03/2015 04/06/2015 10039997 353634 F 62.69 62.69 62.69 EA EA 04/06/2015 --/--/---- 10039997 364734 B 62.69 62.69 62.69 EA EA 04/06/2015 05/06/2015 10039997 364734 F 62.69 62.69 62.69 EA EA 05/06/2015 --/--/---- 10039997 375782 B 62.69 62.69 62.69 EA EA 05/06/2015 06/06/2015 10039997 375782 F 62.69 62.69 62.69 EA EA 06/06/2015 --/--/---- As you can see there are two actions - B is the position before the update and F is the position after. To generate my table I took **Net Price 1** which is the price at the time the log entry was written and took the value in **Cur Price Effective** as my **ValidFrom** date. I then looked for changes to **New Net Prices 1** and used the **Price Effective From** minus 1 for the current line and the actual date for the **ValidFrom** date on the new line with the new price. How do I code this to build the history for the full table? **Edit** I have now filtered where **Price Effective From** '--/--/----' which gives me the following data: Product Code Log Action Number Log Action Type Net Price 1 New Net Prices 1 New Retail Price Sales Unit New Sales Unit Cur Price Effective Price Effective From 10039997 283777 B 0 25.86 25.86 EA EA 31/01/2014 05/03/2015 10039997 353634 B 62.69 62.69 62.69 EA EA 05/03/2015 04/06/2015 10039997 364734 B 62.69 62.69 62.69 EA EA 04/06/2015 05/06/2015 10039997 375782 B 62.69 62.69 62.69 EA EA 05/06/2015 06/06/2015 Somehow I need to take the first line's **Net Price 1** with the **Cur Price Effective** for **ValidFrom** and **New Net Prices 1** at the same time then just look for changes to **New Net Prices 1** I think?
If you have SQL Server 2012 or more recent, you can use LAG/LEAD window functions. I can't test this, but try something like this and let us know if it helps. It's based on your final output table without the "--/--/----" rows. When there is no lead (next) row, ValidTo will be null. SELECT [Product Code], Price , ValidFrom = [Cur Price Effective] , ValidTo = LEAD([Cur Price Effective]) OVER (ORDER BY [Log Action Number]) FROM [your log table] ORDER BY [Product Code], [Log Action Number] ;
18 People are following this question.
