This discussion is archived
6 Replies Latest reply: Feb 8, 2013 7:11 AM by Solomon Yakobson

# SQL: How to cumulatively sum up values till a condition is satisfied?

Currently Being Moderated
Hello all,

I need to write a SQL to fulfill a particular requirement.
For each row, the values in Field2 should be cumulatively summed up to see when it will be equal to the value of Field1
Eg. In the following example, Field1 has 15. Field2 values will be summed up cumulatively till they reach 15 (03-JAN-2013)
For the next record Field1 is 8, so Field2 can match 8 on 05-JAN-13.

Day Field1 Field2
01-JAN-2013 15 10
02-JAN-2013 8 4
03-JAN-2013 9 1
04-JAN-2013 12 2
05-JAN-2013 7 2

Can this be achieved through SQL?
Using Analytical Functions?

Regards,
Sam

Edited by: sammydude on Feb 7, 2013 5:24 AM
• ###### 1. Re: SQL: How to cumulatively sum up values till a condition is satisfied?
Currently Being Moderated
sammydude wrote:
Hello all,

I need to write a SQL to fulfill a particular requirement.
For each row, the values in Field2 should be cumulatively summed up to see when it will be equal to the value of Field1
Eg. In the following example, Field1 has 15. Field2 values will be summed up cumulatively till they reach 15 (03-JAN-2013)
For the next record Field1 is 8, so Field2 can match 8 on 05-JAN-13.

Day Field1 Field2
01-JAN-2013 15 10
02-JAN-2013 8 4
03-JAN-2013 9 1
04-JAN-2013 12 2
05-JAN-2013 7 2

Can this be achieved through SQL?
Using Analytical Functions?

Regards,
Sam

Edited by: sammydude on Feb 7, 2013 5:24 AM
What is the expected output?
What is the sum of field2 does not match precisely field1 (i.e is greater or less)?

Regards.
Al
• ###### 2. Re: SQL: How to cumulatively sum up values till a condition is satisfied?
Currently Being Moderated
I would probably need the difference in days between the current row's date and the date on which Field1 value will be met.
If cumulative Field2 value is greater than Field1, then we stop searching.
If cumulative Field2 value is greater than Field1, then we continue to the following day.

Thanks,
Sam
• ###### 3. Re: SQL: How to cumulatively sum up values till a condition is satisfied?
Currently Being Moderated
Hi,

Try this:
``````with tab as
(
select to_date('01-JAN-2013','dd-mon-yyyy') Day, 15 Field1, 10 Field2 from dual union all
select to_date('02-JAN-2013','dd-mon-yyyy') Day, 8  Field1, 4  Field2 from dual union all
select to_date('03-JAN-2013','dd-mon-yyyy') Day, 9  Field1, 1  Field2 from dual union all
select to_date('04-JAN-2013','dd-mon-yyyy') Day, 12 Field1, 2  Field2 from dual union all
select to_date('05-JAN-2013','dd-mon-yyyy') Day, 7  Field1, 2  Field2 from dual
/*DAY       FIELD1 FIELD2
--------- ------ ------
01-JAN-13     15     10
02-JAN-13      8      4
03-JAN-13      9      1
04-JAN-13     12      2
05-JAN-13      7      2
*/
)
,tab_sum as
(
select
t1.day    t1_day
,t2.day   t2_day
,t1.field1
,t2.field2
,sum(t2.field2) over (partition by t1.day order by t2.day rows between unbounded preceding and current row) sf2

from
tab t1 join
tab t2 on (t1.day <= t2.day)
/*
T1_DAY    T2_DAY    FIELD1 FIELD2 SF2
--------- --------- ------ ------ ---
01-JAN-13 01-JAN-13     15     10  10
01-JAN-13 02-JAN-13     15      4  14
01-JAN-13 03-JAN-13     15      1  15
01-JAN-13 04-JAN-13     15      2  17
01-JAN-13 05-JAN-13     15      2  19
02-JAN-13 02-JAN-13      8      4   4
02-JAN-13 03-JAN-13      8      1   5
02-JAN-13 04-JAN-13      8      2   7
02-JAN-13 05-JAN-13      8      2   9
03-JAN-13 03-JAN-13      9      1   1
03-JAN-13 04-JAN-13      9      2   3
03-JAN-13 05-JAN-13      9      2   5
04-JAN-13 04-JAN-13     12      2   2
04-JAN-13 05-JAN-13     12      2   4
05-JAN-13 05-JAN-13      7      2   2
*/
)

select
t1_day
,max(t2_day)  t2_day
,field1
,max(sf2)     sum_field2

from
tab_sum

where
sf2 <= field1

group by
t1_day
,field1

order by
t1_day
;

T1_DAY    T2_DAY    FIELD1 SUM_FIELD2
--------- --------- ------ ----------
01-JAN-13 03-JAN-13     15         15
02-JAN-13 04-JAN-13      8          7
03-JAN-13 05-JAN-13      9          5
04-JAN-13 05-JAN-13     12          4
05-JAN-13 05-JAN-13      7          2 ``````
Note for the day = 02-jan-2013 you can not reach 8 unles you skip day 03-jan-2013.

Regards,

Peter
• ###### 4. Re: SQL: How to cumulatively sum up values till a condition is satisfied?
Currently Being Moderated
``````with data as (
select date '2013-01-01' d, 15 x,10 y from dual union all
select date '2013-01-02', 8 , 4 from dual union all
select date '2013-01-03', 9,1 from dual union all
select date '2013-01-04', 12,2 from dual union all
select date '2013-01-05', 7,2 from dual)
, sums as (
select
d,x,y,
sum(y) over (order by d) s
from data)

select
s1.d
,s1.x
,s1.y
,min(s2.d) date_reached
,max(s2.s) keep (dense_rank first order by s2.d)
-s1.s + s1.y
sum_reached
from
sums s1
,sums s2
where
s1.x-s1.y <= s2.s-s1.s
group by
s1.d
,s1.x
,s1.y
,s1.s
order by d

D     X     Y     DATE_REACHED     SUM_REACHED
01.01.2013     15     10     03.01.2013     15
02.01.2013     8     4     05.01.2013     9``````
For alternative please give your 4-digit database version, because there is a drawback with this kind of join-based solutions:
Many rows are joined but in fact we need only one (the one with the lowest date).
So i would do it with recursive subquery on 11g and consider model in 10g.

Edited by: chris227 on 07.02.2013 05:59

Edited by: chris227 on 07.02.2013 06:27

Edited by: chris227 on 07.02.2013 06:36
corrected sum_reached

Edited by: chris227 on 07.02.2013 08:34
• ###### 5. Re: SQL: How to cumulatively sum up values till a condition is satisfied?
Currently Being Moderated
sammydude wrote:
Eg. In the following example, Field1 has 15. Field2 values will be summed up cumulatively till they reach 15 (03-JAN-2013)
For the next record Field1 is 8, so Field2 can match 8 on 05-JAN-13.
Is this what you need:
``````with t1 as (
select  day,
field2,
connect_by_root day root_day,
connect_by_root field1 root_field1
from  tbl
connect by day = prior day + 1
),
t2 as (
select  day,
root_day,
root_field1,
field2,
sum(field2) over(partition by root_day order by day) - field2 sum_prev_field2
from  t1
)
select  root_day from_day,
max(day) to_day
from  t2
where root_field1 > sum_prev_field2
and root_field1 <= sum_prev_field2 + field2
group by root_day
order by from_day
/

FROM_DAY  TO_DAY
--------- ---------
01-JAN-13 03-JAN-13
02-JAN-13 05-JAN-13

SQL> ``````
SY.
• ###### 6. Re: SQL: How to cumulatively sum up values till a condition is satisfied?
Currently Being Moderated
Solomon Yakobson wrote:Is this what you need:
Or perhaps:
``````with t1 as (
select  day,
field2,
connect_by_root day root_day,
connect_by_root field1 root_field1,
connect_by_isleaf leaf
from  tbl
connect by day = prior day + 1
),
t2 as (
select  day,
root_day,
root_field1,
field2,
leaf,
sum(field2) over(partition by root_day order by day) - field2 sum_prev_field2
from  t1
)
select  root_day from_day,
max(day) to_day
from  t2
where root_field1 > sum_prev_field2
and (
root_field1 <= sum_prev_field2 + field2
or
leaf = 1
)
group by root_day
order by from_day
/

FROM_DAY  TO_DAY
--------- ---------
01-JAN-13 03-JAN-13
02-JAN-13 05-JAN-13
03-JAN-13 05-JAN-13
04-JAN-13 05-JAN-13
05-JAN-13 05-JAN-13

SQL> ``````
SY.

#### Legend

• Correct Answers - 10 points