'Result based on column value aggregated by month/year & a threshold

I have the following table

Fact table f:

date partner stake
2022/01/01 AA 10
2022/01/01 BB 22
2022/01/01 DD 22
2022/01/02 AA 5
2022/01/02 BB 9
2022/01/10 AA 10
2022/01/31 AA 20
2022/01/31 BB 2
2022/02/01 AA 10

and an external table like this:

Manual_file mf:

date partner value
2022/01/01 AA 10%
2022/01/01 BB 10%
2022/01/02 AA 10%
2022/01/02 BB 10%
2022/01/03 AA 10%
2022/01/04 AA 10%
2022/01/05 AA 10%
2022/01/06 AA 10%
2022/01/07 AA 10%
2022/01/08 AA 10%
2022/01/09 AA 10%
2022/01/08 AA 10%
2022/01/10 AA 10%
2022/01/11 AA 10%
2022/01/12 AA 10%
2022/01/13 AA 10%
2022/01/31 AA 10%
2022/01/31 BB 10%
2022/02/01 AA 10%

I'm performing a left join between the fact and the external tables using (date, partner). Based on this join, I'm multiplying the column f.stake by the percentage from the column mf.value. So far it's pretty simple :


    SELECT 
        f.date, 
        f.partner,
        f.spent,
        CASE
            WHEN MAX(mf.value) = "10%" 
                         THEN COALESCE(SUM(f.Stake) * 0.10, 0)
        END AS budget
    FROM
        fact f
    LEFT JOIN 
        manual_file mf USING (date, partner)

I get the following result:

date partner stake budget
2022/01/01 AA 10 1
2022/01/01 BB 22 2,2
2022/01/01 DD 22 0
2022/01/02 AA 5 0,5
2022/01/02 BB 9 0,9
2022/01/10 AA 10 1
2022/01/31 AA 20 2
2022/01/31 BB 2 0,2
2022/01/31 AA 10 1

Imagine the same query, but with an another rule saying that if the sum of the previous rows (for the same month and for partners AA & BB), is equal or higher than 5, then nothing is happening (budget is 0). I haven't been able to give the expected result just below. I tried some stuff with window function, to group by month/year, but I'm stuck with the fact that I have multiple partners to take care of..

Expected result :

date partner stake budget explaination of budget column
2022/01/01 AA 10 1 sum of previous AA&BB<5=> 10*0,1
2022/01/01 BB 22 2,2 sum of previous AA&BB<5=> 22*0,1
2022/01/01 DD 22 0 no match with mf
2022/01/02 AA 5 0,5 sum of previous AA&BB<5=> 5*0,1
2022/01/02 BB 9 0,9 sum of previous AA&BB<5=> 9*0,1
2022/01/10 AA 10 0,4 sum of previous AA&BB<5 but if I multiply the stake value (10) by 0,1, total will be higher than 5!(1+2,2+0,5+0,9+1 = 5,6) so budget value is 0,4.
2022/01/31 AA 20 0 sum of previous AA&BB=5 => 0
2022/01/31 BB 2 0 sum of previous AA&BB=5 => 0
2022/02/01 AA 10 1 sum of previous AA&BB on february is 0 (<5) so =>10*0.1

Any help would be appreciated to solve this problem,

Thanks!

sqldatabasejoin


Solution 1:[1]

I'm sorry but I haven't really understood the calculation of what you want but here I can show you how to have the sum of rows up to and including the current row. Please adjust to meet your needs.

create table facture (
  fdate date,
  partner char(2),
  stake int,
  spent int);
insert into facture values

('2022/01/01','AA', 10 , 0   ),
('2022/01/01','BB', 22 , 0   ),
('2022/01/01','DD', 22 , 0   ),
('2022/01/02','AA', 5  , 5   ),
('2022/01/02','BB', 9  , 0   ),
('2022/01/02','FF', 9  , 10  ),
('2022/01/10','AA', 10 , 0   ),
('2022/01/22','BB', 20 , 0   ),
('2022/01/31','AA', 20 , 0   ),
('2022/01/31','BB', 2  , 0   );

create table manual (
  mdate date,
  partner char(2),
  value int);
inset into manual values
('2022/01/01','AA', 10 ),
('2022/01/01','BB', 10 ),
('2022/01/02','AA', 10 ),
('2022/01/02','BB', 10 ),
('2022/01/10','AA', 10 ),
('2022/01/22','BB', 10 ),
('2022/01/31','AA', 10 ),
('2022/01/31','BB', 10 );

?

?

?

with frank as
(select f.partner , f.fdate, 
sum(f.stake) sumStake,
sum(f.spent) sumSpent,
rank() over (partition by f.partner
             order by f.fdate) ranking
from facture f 
group by f.partner, f.fdate)

select 
  f.partner,
  f.fdate,
  f.ranking,
  f.sumStake,
  (select sum(sumStake)from frank k where k.ranking<=f.ranking and k.partner=f.partner) cumulStake,
  f.sumSpent,
  (select sum(sumSpent)from frank k where k.ranking<=f.ranking and k.partner=f.partner) cumulSpent,
  (select sum(sumStake) - sum(sumSpent)from frank k where k.ranking<=f.ranking and k.partner=f.partner) budgetLeft
from frank f
left join manual m 
on f.fdate=m.mdate
and f.partner=m.partner
order by fdate, f.partner;

/*SELECT (SELECT SUM(asum) FROM cteRanked c2 WHERE c2.rownum <= c1.rownum),*/

partner | fdate      | ranking | sumStake | cumulStake | sumSpent | cumulSpent | budgetLeft
:------ | :--------- | ------: | -------: | ---------: | -------: | ---------: | ---------:
AA      | 2022-01-01 |       1 |       10 |         10 |        0 |          0 |         10
BB      | 2022-01-01 |       1 |       22 |         22 |        0 |          0 |         22
DD      | 2022-01-01 |       1 |       22 |         22 |        0 |          0 |         22
AA      | 2022-01-02 |       2 |        5 |         15 |        5 |          5 |         10
BB      | 2022-01-02 |       2 |        9 |         31 |        0 |          0 |         31
FF      | 2022-01-02 |       1 |        9 |          9 |       10 |         10 |         -1
AA      | 2022-01-10 |       3 |       10 |         25 |        0 |          5 |         20
BB      | 2022-01-22 |       3 |       20 |         51 |        0 |          0 |         51
AA      | 2022-01-31 |       4 |       20 |         45 |        0 |          5 |         40
BB      | 2022-01-31 |       4 |        2 |         53 |        0 |          0 |         53

?

db<>fiddle here

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions