Add New Record Value, & Previous Record Value MySQL-CodePudding

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

CodePudding user response：

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.