CODEWITHSUNDEEP
sql-servert-sqldata-migration
MSB
MSB

Reputation: 884

Migrating SQL Server point history table to period history table

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.

Upvotes: 0

Views: 74

Answers (2)

Horaciux
Horaciux

Reputation: 6477

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

Upvotes: 1

Aron Hennerdal
Aron Hennerdal

Reputation: 121

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

Upvotes: 1

Related Questions