SUM over time

Time is continuous and time periods are modelled in SQL tables with validFrom validTill columns. I like to see continous pair stored as open ended, validFrom moment is included in the period, but validTill moment is excluded. This way it is possible to store the whole timeline.

Lets have a relation having such a pair

9.4.2009 10.4.2009 1

10.4.2009 12.4.2009 2

11.4.2009 14.4.2009 3

As one can see two last rows overlaps and for 11.4. there is all together 5 as a sum. Here is a SQL how to generate a sum for each moment of time.

with td as (
 select trunc(sysdate) + mod(level, 4) validFrom
      , trunc(sysdate) + mod(level, 8) * 2 validTill
      , level a
   from dual
connect by level < 4
)
select validFrom
     , nvl(lead(validFrom) over (order by validFrom)
         , to_date('22000101','yyyymmdd')) validTill
     , sumovertime
  from (
 select validFrom, max(sumover) sumovertime
   from (
  select a
   , case r when 1 then validFrom else validTill end validFrom
   , sum(case r when 1 then a else -a end) over (order by case r when 1 then validFrom else validTill end,r desc) sumover
   from td, (
  select rownum r from dual connect by level < 3
  )
 )
 group by validFrom
)
order by validFrom
;

9.4.2009 10.4.2009 1

10.4.2009 11.4.2009 2

11.4.2009 12.4.2009 5

12.4.2009 14.4.2009 3

14.4.2009 1.1.2200 0