Rolling sum per time interval per group

Question

Table, data and task as follows. See SQL-Fiddle-Link for demo-data and estimated results.

create table "data" 
(
  "item" int
  , "timestamp" date
  , "balance" float
  , "rollingSum" float
)

insert into "data" ( "item", "timestamp", "balance", "rollingSum" ) values
  ( 1, '2014-02-10', -10, -10 )
, ( 1, '2014-02-15',   5,  -5 )
, ( 1, '2014-02-20',   2,  -3 )
, ( 1, '2014-02-25',  13,  10 )

, ( 2, '2014-02-13',  15,  15 )
, ( 2, '2014-02-16',  15,  30 )
, ( 2, '2014-03-01',  15,  45 )

I need to get all rows in an defined time interval. The above table doesn't hold a record per item for each possible date - only dates on which changes applied are recorded ( it is possible that there are n rows per timestamp per item ) If the given interval does not fit exactly on stored timestamps, the latest timestamp before startdate ( nearest smallest neighbour ) should be used as start-balance/rolling-sum.

estimated results ( time interval: startdate = '2014-02-13', enddate = '2014-02-20' )

"item", "timestamp"  , "balance", "rollingSum"
 1    , '2014-02-13' , -10      , -10
 1    , '2014-02-15' ,   5      ,  -5
 1    , '2014-02-20' ,   2      ,  -3
 2    , '2014-02-13' ,  15      ,  15
 2    , '2014-02-16' ,  15      ,  30

I checked questions like this and googled a lot, but didn't found a solution yet.

I don't think it's a good idea to extend "data" table with one row per missing date per item, thus the complete interval ( smallest date <-----> latest date per item may expand over several years ).

Thanks in advance!

paparazzo · Answer 1 · 2014-03-10T14:40:39.843

0

select sum(balance)  
from table  
where timestamp >= (select max(timestamp) from table where timestamp <= 'startdate')
  and timestamp <= 'enddate'

Don't know what you mean by rolling-sum.

edited Mar 10 '14 at 14:40

answered Mar 10 '14 at 14:18

paparazzo

44,497
23
105
176

Rolling sum: The cumulated sum of all preprocessors and the current value per row. Also known as running total or cumulative sum. – Nico Mar 10 '14 at 14:28
Unfortunately, your query does not work and won't show desired results as shown in fiddle. – Nico Mar 10 '14 at 14:35
Preprocessors? Do you mean predecessors? What have you tried? – paparazzo Mar 10 '14 at 14:40
Yes, my fault. Can't edit my comment, but I meant **predecessors**. – Nico Mar 10 '14 at 14:58

score 0 · Answer 2 · answered Mar 10 '14 at 15:45

here is an attempt. Seems it gives the right result, not so beautiful. Would have been easier in sqlserver 2012+:

declare @from date = '2014-02-13' 
declare @to date = '2014-02-20'
;with x as
(
select 
item, timestamp, balance, row_number() over (partition by item order by timestamp, balance) rn
from (select item, timestamp, balance from data 
union all
select distinct item, @from, null from data) z
where timestamp <= @to
)
, y as
(
  select item, 
  timestamp, 
  coalesce(balance, rollingsum) balance , 
  a.rollingsum, 
  rn
from x d
cross apply
(select sum(balance) rollingsum from x where rn <= d.rn and d.item = item) a
where timestamp between '2014-02-13' and '2014-02-20'

)
select item, timestamp, balance, rollingsum from y
where rollingsum is not null
order by item, rn, timestamp

Result:

item  timestamp   balance  rollingsum
1     2014-02-13  -10,00   -10,00
1     2014-02-15  5,00     -5,00
1     2014-02-20  2,00     -3,00
2     2014-02-13  15,00    15,00
2     2014-02-16  15,00    30,00

Rolling sum per time interval per group

2 Answers2