I am busy creating a new table in my DB, and have some issues populating the correct values.
The table consists of a number of columns
| Date | CustomerID | SKUCode | NewValueCaptured |PreviousDate| PreviousValueCaptured | |:----------|:-----------|:--------|:-----------------|:-----------|-----------------------| | 2022-07-01| 123456 | 1028 | 10 | NULL | NULL | | 2022-07-09| 123456 | 1028 | 15 | 2022-07-01 | 10 | | 2022-07-12| 123456 | 1028 | 25 | 2022-07-01 | 15 | | 2022-07-12| 123456 | 1029 | 8 | NULL | NULL | | 2022-07-01| 789123 | 1028 | 20 | NULL | NULL | | 2022-07-09| 789123 | 1028 | 10 | 2022-07-01 | 20 | | 2022-07-01| 789123 | 1029 | 25 | NULL | NULL | | 2022-07-09| 789123 | 1029 | 13 | 2022-07-01 | 25 |
Using the UPDATE ON DUPLICATE KEY, is not an option here, as I need to keep each and every record, however, adding only the previous value to the new record.
Existing Query:
INSERT IGNORE INTO CS_data (Date, CustomerID , SKUCode, NewValueCaptured, PreviousDate, PreviousValueCaptured) SELECT * FROM (SELECT DISTINCT DWH.Date 'SRCDate' , DWH.CustomerID , CASE WHEN DWH.SKUCode IS NOT NULL THEN DWH.SKUCode ELSE DWH.SKUCode END 'SKUCode' , DWH.ValueCaptured 'SRCValueCaptured' ) SRC ON DUPLICATE KEY UPDATE PreviousDate = Date , PreviousValueCaptured = NewValueCaptured , Date = SRCDate , NewValueCaptured= SRCValueCaptured;
How do I achieve the above table results? Rather than updating the existing record.
Thanks
Advertisement
Answer
It seems that you need in something like (demo only)
INSERT INTO destination_table ( Date, CustomerID, SKUCode, NewValueCaptured, PreviousDate, PreviousValueCaptured ) SELECT Date, CustomerID, SKUCode, ValueCaptured, -- get Date value from previous row, if not exists use the value from current row COALESCE(LAG(Date) OVER (PARTITION BY CustomerID, SKUCode ORDER BY Date), Date), -- and the same for ValueCaptured COALESCE(LAG(ValueCaptured) OVER (PARTITION BY CustomerID, SKUCode ORDER BY Date), ValueCaptured) FROM source_table;
Documentation: Window functions.