Filling the gaps

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". ;)

ML