2009-04-08

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



1 comment:

About Me

My photo
I am Timo Raitalaakso. I have been working since 2001 at Solita Oy as a Senior Database Specialist. I have received Oracle ACE nomination. My main focus is on projects involving Oracle database. In this Rafu on db blog I write some interesting issues that evolves from my interaction with databases. Mainly Oracle.