Insert into history table and selecting validFrom and validTo dates based on table contents
Edit: Using SQL-Server 2008
I have an AccountHistory table that has multiple columns. Relevant for this case is Account_FK, ValidTo, ValidFrom
Im trying to insert new values into this table by using:
INSERT INTO AccountHistory (Account_FK, ValidTo, ValidFrom) SELECT Account_FK, ValidTo, ValidFrom FROM someOtherTable
Problem is: If there is an existing row with the same Account_FK in AccountHistory, the inserted row should have its ValidFrom date set to current date. ValidTo should be null. And the old AccountHistory-row should have its ValidTo date updated from null to current date. If there is no other row with the same Account_FK then the validFrom date should be set to a system-default start date, e.g. 1990-01-01
How is this most easily and elegantly solved? I know a trigger might be the thing, but if there is a better way then i would like to hear your suggestions.
ValidFrom field is a not null field so i cant insert any null into it.
The simplest answer is to remove the ValidTo field, which is effectively a derived field (which you're usually not supposed to store). Then, you only have to insert the relevant entries, and can derive the needed value fairly trivially (requires aggregated self-join).
However, I'm a little concerned that you're overriding the given ValidFrom date if an entry exists - you're effectively making it impossible to back-date history, and that new entries will always be 'new, and currently in effect', which is extremely unlikely. For one thing, if you ever insert multiple entries, you may not have a garuanteed insert order, which will promptly play havoc with this. It also makes it impossible to back-date entries, if necessary (note: this isn't necessarily for nefarious purposes - there are occasionally good business reasons to backdate 'eligible' history. Include an entry timestamp, for auditing, though). Also, what happens when you have multiple entries for the same day - how would you know which one was correct (so are you sure you don't actually want a timestamp)?
Other than that, here's the two statements (tested on DB2) I would use to modify the table as you described. First, an insert statement to take care of the new entries:
INSERT INTO AccountHistory (Account_FK, ValidTo, ValidFrom) SELECT a.Account_FK, CASE WHEN b.Account_FK IS NULL THEN a.ValidTo ELSE NULL END, CASE WHEN b.Account_FK IS NULL THEN a.ValidFrom ELSE CURRENT_DATE END FROM SomeOtherTable as a LEFT JOIN (SELECT DISTINCT Account_FK FROM AccountHistory) as b ON b.Account_FK = a.Account_FK
And an update statement to populate 'ValidTo' dates (this gets everything, not just what was inserted):
UPDATE AccountHistory as a SET ValidTo = (SELECT MIN(b.ValidFrom) FROM AccountHistory as b WHERE a.Account_FK = b.Account_FK AND b.ValidFrom > a.ValidFrom) WHERE ValidTo IS NULL AND EXISTS (SELECT '1' FROM AccountHistory as b WHERE a.Account_FK = b.Account_FK AND b.ValidFrom > a.ValidFrom)
If using 2008 you can use the merge statement - Solutions for INSERT OR UPDATE on SQL Server - see Keith's answer about a third of the way down. Would that work?