Looking for advice on how I go about writing this code.
I have a table of unique values being ported over from one system to another. And I want to store a record of say the last 5 dates of when the record was brought over.
So for example, Item A is refreshed from System X to System Y on the 1st October 2017, I want to store this date in the table. Then over the next month, it is brought over another 4 times, so I want these date values also written into that table.
Now that the last 5 dates have been written, I need some way of maintaining the code so that any new dates that come in will overwrite the table data, so that we will always have at most the last 5 update dates in this table.
Example Data; The date columns represent the last 5 updates that have occured for each property row.
Property ID's, Date 1, Date 2, Date 3, Date 4, Date 5
1 01/07/17, 01/08/17 01/10/17 05/10/17 10/10/17
2 01/01/17 01/03/17 01/06/17 05/10/17 10/10/17
3 01/02/17 05/02/17 01/10/17 05/10/17 10/10/17
4 01/03/17 01/08/17 01/10/17
If property 4 has an update from system X, date 4 gets populated. If property 3 has an update from system x, Dates 2 to 5 would move left one position and date 5 would get populated with the latest date.
Would this be the best way of recording last 5 updates.
Alternatively, i could just write each property id and date of update into a table and have some sort of cleaning routine of only keeping 5 entries per property.
Apologies if I'm being vague.
Better use table like:
CREATE TABLE dbo.ChangeDates (
PropertyID int,
DateChanged date
)
And just write data in it. Then create view like:
CREATE VIEW dbo.Top5ChangeDates
AS
WITH cte AS (
SELECT PropertyID,
DateChanged,
ROW_NUMBER() OVER (PARTITION BY PropertyID ORDER BY DateChanged DESC) as rn
FROM dbo.ChangeDates
)
SELECT PropertyID,
DateChanged
FROM cte
WHERE rn <= 5
GO
Or use after insert TRIGGER instead of view, to clear data.