Insert into history table and selecting validFrom and validTo dates based on table contents

Question

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.

Which version of SQL Server please? SQL Server 2008+ gives you MERGE http://technet.microsoft.com/en-us/library/bb510625.aspx — gbn, Nov 30 '11 at 15:43
While a trigger seems nice, you'd be embedding business logic in it... what happens if the trigger fails? I think a procedure with the embedded business logic to make the necessary updates where you can capture the errors and handle them more eloquently seems like a better choice. Just my 2 cents — xQbert, Nov 30 '11 at 15:45
Sorry, Im using SQL Server 2008, just edited the question with this info! — user829237, Nov 30 '11 at 15:49
Btw. I dont think the MERGE is a viable solution. Since the AccountHistory table might contain lots of rows with the Same Account_FK, only differed by validTo and ValidFrom dates. Matching might produce multiple results and i would need to pick the top date result of the matches, which dont think i can do with merge? — user829237, Nov 30 '11 at 15:52

score 0 · Answer 1 · edited May 23 '17 at 12:20

0

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?

edited May 23 '17 at 12:20

Community

1
1

answered Nov 30 '11 at 15:49

SGB

616
6
10

I dont think the MERGE is a viable solution. Since the AccountHistory table might contain lots of rows with the Same Account_FK, only differed by validTo and ValidFrom dates. Matching might produce multiple results and i would need to pick the top date result of the matches, which dont think i can do with merge? – user829237 Nov 30 '11 at 15:53
yup that could be tricky reading your question again. I just wondered if you could just use a single date column in your history table, saving you having to seek the previous record and update it - which like you said does sound to me like a natural job for a trigger. (as long as I have read your question correctly!) Every new record will have a FromDate that matches the previous record's ToDate - so could fromDate be recorded alone with just inserts? The ranges can still be extracted from this data. Apologies if misunderstood! – SGB Nov 30 '11 at 16:25
Good point. That would solve a lot of headache. I'll check with our DBA if he would let me do that. – user829237 Dec 01 '11 at 11:12

score 0 · Accepted Answer · answered Nov 30 '11 at 17:04

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)

Hello and thanks for your solution! This looks like it would work like a charm and i will take your suggestions and test them on our system. It is however a complicated process fit your solution into ours since i'm creating a dynamic query based on quite a few factors and the self-join could be a bit tricky to implement. But i'll look into it and post back soon :) As for your concern about overriding the given ValidFrom-date. I agree, but actually i do not have a given validFrom-date, i just wrote it for simplicities sake. And i agree about your other concerns. — user829237, Dec 01 '11 at 09:53

Insert into history table and selecting validFrom and validTo dates based on table contents

2 Answers2