the historical records in the database were closed

If I’m understanding your question correctly, this is actually very simple to accomplish in any database. The “historical” data is identified by a “lifespan” (not the technical name for it). This would translate to a “born” and “died” record values or “begin” and “end” datetime columns.

Using Azure SQL as an example, create your table with the begin & end datetime fields:

create table company_prices (
company_name nvarchar(55),
product_name nvarchar(55),
price money,
begin_timestamp datetime,
end_timestamp datetime )

When you insert your records for the first time, use a date value for the begin datetime field and leave the end datetime field null:

insert into company_prices 
select 'Company A', 'Widget 1', 13.57, getdate(), NULL
union
select 'Company B', 'Widget 2', 24.68, getdate(), NULL
union
select 'Company C', 'Widget 3', 3.69, getdate(), NULL

Now you have three “active” records. They are active because they do not have an end datetime assigned.

The next time you want to collect your prices, update the previous or historical record by assigning an end datetime value before inserting any new records.

update company_prices 
set end_timestamp = getdate() 
where company_name = 'Company A' 
and product_name = 'Widget 1' 
and end_timestamp is null

Now, when you insert your new price you’ll have a historical record and a current/active record for Company A, Widget 1.

company_name    product_name    price   begin_timestamp         end_timestamp
Company A       Widget 1        13.57   2020-11-05 03:19:11.027 2020-11-05 04:19:11.030
Company A       Widget 1        13.95   2020-11-05 04:19:11.030 NULL
Company B       Widget 2        24.68   2020-11-05 03:19:11.027 NULL
Company C       Widget 3         3.69   2020-11-05 03:19:11.027 NULL

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top