I'm struggling with building an UPSERT
script that on conflict (1) modifies the existing record to avoid a conflict and then (2) inserts the new record. I am trying to build a table that maintains point in time fidelity while conserving space. The theory being that it is more efficient to properly capture the change in data as opposed to capturing duplicate slices of data that doesn't change.
I have built an example to help point out what I am trying to do. There are three race car drivers that are at a certain track for a certain period of time. One of the drivers decides to change tracks while the other two decide to stay where they are. At some later point in time, a fourth driver enters the picture.
I would like to be able to query the table and say "who was at what track on this day?"
Hopefully this makes sense.
Thanks in advance.
----------------------Set up an example table
create table drivers(
firstName varchar(30)
,lastName varchar(30)
,raceTrack varchar(30)
,effectiveDate date
,expirationDate date
);
insert into drivers
select 'ricky', 'bobby', 'talladega', cast('2019-01-01' as date), cast('9999-12-31' as date)
union
select 'cal', 'naughton', 'martinsville', cast('2019-01-01' as date), cast('9999-12-31' as date)
union
select 'jean', 'girard', 'daytona', cast('2019-01-01' as date), cast('9999-12-31' as date);
----------------------Expire the daytona record for Jean
----This is the UPDATE portion of the UPSERT and triggers a conflict with the UPSERT since firstname and lastname match.
update drivers set expirationdate = cast('2019-02-10' as date)
where firstname = 'jean' and lastname = 'girard';
----------------------Insert new circuit de monaco record for Jean
----This is the INSERT portion of the UPSERT
insert into drivers
select 'jean', 'girard', 'circuit de monaco', cast('2019-02-11' as date), cast('9999-12-31' as date);
----------------------A new driver comes into the data
----This is an example of the expected behavior without a conflict
insert into drivers
select 'reese', 'bobby', 'greenville pickens', cast('2019-02-12' as date), cast('9999-12-31' as date);
----------------------Query all of the driver and track records for some day in January 2019
select *
from drivers
where cast('2019-01-11' as date) between effectivedate and expirationdate;
----------------------Query all of the driver and track records for some day in February 2019
select *
from drivers
where cast('2019-02-11' as date) between effectivedate and expirationdate;
select *
from drivers
where cast('2019-02-12' as date) between effectivedate and expirationdate;