Hi, I am currently building a data warehouse and have an issue with records to delete from the fact table.
The source data does not update records but rather creates a new row for the change and uses an audit number together with other columns to form a composite key. I am using the following code to get the most recent record as the row may have been changed several times since the last load:
SELECT * FROM (
SELECT * ROW_NUMBER() OVER (PARTITION BY [SampleCode] ORDER BY [AuditNumber] desc) rn
) tmp WHERE rn = 1
AND AuditNumber IS NOT NULL;
(In the live code the column are listed)
If this returns the 4th version of the row, I need to be able to identify the previous 3 and remove them from the fact table.