Migrating SQL Server point history table to period history table

Question

I am trying to clean up a not so useful history table by changing it's format. For the usage of the history table it is relevant between which time a row was valid.

The current situation:

Unit | Value | HistoryOn | ---------------------------------------- 1 | 123 | 2013-01-05 14:16:00 1 | 234 | 2013-01-07 12:12:00 2 | 325 | 2013-01-04 14:12:00 1 | 657 | 2013-02-04 17:11:00 3 | 132 | 2013-04-02 13:00:00

The problem that arises here is that as this table grows it will become increasingly resource hungry when I want to know what status all of my containers had during a certain period. (say I want to know the value for all units on a specific date)

My solution is to create a table in this format:

Unit | value | HistoryStart | HistoryEnd | --------------------------------------------------------------------- 1 | 123 | 2013-01-05 14:16:00 | 2013-01-07 12:11:59 1 | 234 | 2013-01-07 12:12:00 | 2013-02-04 17:10:59 1 | 657 | 2013-02-04 17:11:00 | NULL 2 | 325 | 2013-01-04 14:12:00 | NULL 3 | 132 | 2013-04-02 13:00:00 | NULL

Note that the NULL value in HistoryEnd here indicates that the row is still representative of the current status.

I have tried to make use of a left join on the table itself using the HistoryOn field. This had the unfortunate side effect of cascading in an undesired manner.

SQL Query used:

SELECT * FROM webhistory.Units u1 LEFT JOIN webhistory.Units u2 on u1.Unit = u2.Unit AND u1.HistoryOn < u2.HistoryOn WHERE u1.Units = 1

The result of the query is as follows:

 Unit | Value | HistoryOn | Unit | Value | HistoryOn | ------------------------------------------------------------------------------------- 1 | 657 | 2013-02-04 17:11:00 | NULL | NULL | NULL 1 | 234 | 2013-01-07 12:12:00 | 1 | 657 | 2013-02-04 17:11:00 1 | 123 | 2013-01-05 14:16:00 | 1 | 657 | 2013-02-04 17:11:00 1 | 123 | 2013-01-05 14:16:00 | 1 | 234 | 2013-01-07 12:12:00

This effect is incremental because each entry will join on all the entries that are newer than itself instead of only the first entry that comes after it.

Sadly right as of yet I am unable to come up with a good query to solve this and would like insights or suggestions that could help me solve this migration problem.

Aron Hennerdal · Accepted Answer · 2014-08-28 14:42:52Z

Maybe I'm missing something, but this seems to work:

CREATE TABLE #webhist( Unit int, Value int, HistoryOn datetime ) INSERT INTO #webhist VALUES (1, 123, '2013-01-05 14:16:00'), (1, 234, '2013-01-07 12:12:00'), (2, 325, '2013-01-04 14:12:00'), (1, 657, '2013-02-04 17:11:00'), (3, 132, '2013-04-02 13:00:00') SELECT u1.Unit ,u1.Value ,u1.HistoryOn AS HistoryStart ,u2.HistoryOn AS HistoryEnd FROM #webhist u1 OUTER APPLY ( SELECT TOP 1 * FROM #webhist u2 WHERE u1.Unit = u2.Unit AND u1.HistoryOn < u2.HistoryOn ORDER BY HistoryOn ) u2 DROP TABLE #webhist

Thank you for the answer, while I ended up not using this as my final solution it did make me see where I was going wrong. Instead of the OUTER APPLY that you suggested I used a nested select like so: select *, (select top 1 historyon from #webhist u2 where u2.historyon > u1.historyon and u1.unit = u2.unit) from #webhist u1;

Horaciux · Accepted Answer · 2014-08-29 12:51:24Z

First data sample

create table Data( Unit int, Value int, HistoryOn datetime) insert into Data select 1,123,'2013-01-05 14:16:00' union select 1 , 234 , '2013-01-07 12:12:00' union select 2 , 325 , '2013-01-04 14:12:00' union select 1 , 657 , '2013-02-04 17:11:00' union select 3 , 132 , '2013-04-02 13:00:00'

I created a function to calculate HistoryEnd Noticed I named Data to table

CREATE FUNCTION dbo.fnHistoryEnd ( @Unit as int, @HistoryOn as datetime ) RETURNS datetime AS BEGIN -- Declare the return variable here DECLARE @HistoryEnd as datetime select top 1 @HistoryEnd=dateadd(s,-1,d.HistoryOn ) from Data d where d.HistoryOn>@HistoryOn and d.Unit=@Unit order by d.HistoryOn asc RETURN @HistoryEnd END GO

Then, the query is trivial

select *,dbo.fnHistoryEnd(a.Unit,a.HistoryOn) from Data a order by Unit, HistoryOn

EDIT

Don't forget order by clause in sub query. Look what could happen if not

CREATE TABLE #webhist( Unit int, Value int, HistoryOn datetime ) INSERT INTO #webhist VALUES (1, 234, '2013-01-07 12:12:00'), (2, 325, '2013-01-04 14:12:00'), (1, 657, '2013-02-04 17:11:00'), (3, 132, '2013-04-02 13:00:00'), (1, 123, '2013-01-05 14:16:00') select *, (select top 1 historyon from #webhist u2 where u2.historyon > u1.historyon and u1.unit = u2.unit) from #webhist u1; select *, (select top 1 historyon from #webhist u2 where u2.historyon > u1.historyon and u1.unit = u2.unit order by u2.HistoryOn) from #webhist u1; drop table #webhist

Thank you for answering while your method works I ended up using a different approach instead.
@MSB At first I was using your approach, but I can't make it work in sqlfiddle. So I put the sub querry in a function. Don't forget order by u2.HistoryOn in your subquerry as I did in my function. Look at my edit to clarify.

Collectives™ on Stack Overflow

Migrating SQL Server point history table to period history table

2 Answers 2

2 Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Related