I have a table, which when sorted according to the week number gives the units left of a product at a store. The units left should always be decreasing. However, there are some garbage values due to which the units left in a store increases for few weeks and then decreases again. I just have these four columns to work with. I want to replace the garbage values with the correct value. I am looking for the SQL for the following garbage value replacement logic – the units left for each week should be the minimum of the values in units left of all rows above it sorted by week number ascending.
e.g. here it goes to 12 for week 4 and 5 and then back to 9 – which is incorrect – they [the 12s] should each be replaced by 9
INPUT:—
+-------+------------+-------------+------------+ | Store | Product ID | Week Number | Units left | +-------+------------+-------------+------------+ | XXX | A1 | 1 | 10.0 | | XXX | A1 | 2 | 9 | | XXX | A1 | 3 | 9 | | XXX | A1 | 4 | 12 | | XXX | A1 | 5 | 12 | | XXX | A1 | 6 | 9 | | XXX | A1 | 7 | 8 | +-------+------------+-------------+------------+
OUTPUT:—-
+-------+------------+-------------+------------+ | Store | Product ID | Week Number | Units left | +-------+------------+-------------+------------+ | XXX | A1 | 1 | 10.0 | | XXX | A1 | 2 | 9 | | XXX | A1 | 3 | 9 | | XXX | A1 | 4 | 9 | | XXX | A1 | 5 | 9 | | XXX | A1 | 6 | 9 | | XXX | A1 | 7 | 8 | +-------+------------+-------------+------------+
The DB is Teradata.
Advertisement
Answer
You could try cumulative minimum function in teradata.
Select Store, Product_ID, Week_Number, Units, MIN(Units) over (PARTITION BY Store, Product_ID ORDER BY Week_Number ROWS UNBOUNDED PRECEDING) as Corrected_units from TABLE_NAME;