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.

Answers


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?


Need Your Help

How to restrict my app to landscape mode?

iphone cocoa-touch ipad orientation landscape

I have my iPad application created using the SplitView template.

How to get values from dropdownlist to bind, when this dropdownlist is from a UserControl?

.net vb.net drop-down-menu user-controls bind

I have 3 dropdownlist (cbb_state, cbb_products, cbb_item) in my UserControl (UCProducs), which have a DataSource each one (ods_state, ods_products, ods_item), and I have my .aspx page, that is usin...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.