Recently, in a newsgroup discussion, once again the subject of 'filling the gaps' came up.
Imagine an object that can exist in several different states, each of these can last a certain period of time. In order to preserve space and to avoid redundancy we only trace the changes of the object's state. For reporting purposes, however, we require contiguity - the question is no longer when has the state changed but rather what was the state at a given time.
Certainly there are other more efficient methods of providing an answer to this question, nonetheless this one was actually requested by the newsgroup user.
In this example we'll take advantage of the very useful calendar table. You can find more on the subject here.
First, our event tracing table:
create table dbo.Gaps ( EventTime datetime not null ,EventName varchar(32) not null ) go insert dbo.Gaps ( EventTime ,EventName ) select '2006-01-01' as EventTime ,'hangover' as EventName union all select '2006-01-07' ,'hungry' union all select '2006-01-10' ,'thirsty' go
Second, a simplified calendar table:
create table dbo.Contiguous ( DT datetime not null ) go insert dbo.Contiguous ( DT ) select '2006-01-01' as DT union all select '2006-01-02' union all select '2006-01-03' union all select '2006-01-04' union all select '2006-01-05' union all select '2006-01-06' union all select '2006-01-07' union all select '2006-01-08' union all select '2006-01-09' union all select '2006-01-10' union all select '2006-01-11' go
I don't favor redundancy, so rather than adding new rows to the table to fill the gaps I decided on using a view instead:
create view dbo.NoGaps as select dbo.Contiguous.DT as EventTime ,EventName = ( select top 1 RunningEvents.EventName from dbo.Gaps RunningEvents where (RunningEvents.EventTime <= dbo.Contiguous.DT) order by RunningEvents.EventTime desc ) from dbo.Gaps right outer join dbo.Contiguous on dbo.Contiguous.DT = dbo.Gaps.EventTime go
It's quite simple, really: the contiguous datetime values from the calendar table serve as a backbone against which the object's states are now set as they've progressed through time.
EventTime EventName ----------------------- -------------------------------- 2006-01-01 00:00:00.000 hangover 2006-01-02 00:00:00.000 hangover 2006-01-03 00:00:00.000 hangover 2006-01-04 00:00:00.000 hangover 2006-01-05 00:00:00.000 hangover 2006-01-06 00:00:00.000 hangover 2006-01-07 00:00:00.000 hungry 2006-01-08 00:00:00.000 hungry 2006-01-09 00:00:00.000 hungry 2006-01-10 00:00:00.000 thirsty 2006-01-11 00:00:00.000 thirsty (11 row(s) affected)
And in true Spinal Tap fashion the numbers in this example "go up to eleven". ;)