I am trying to obtain or calculate the information of each record within a set (installment and partial value), I explain below with an example:
| pay | date | value |
|:-----------|------------:|:-----:|
| 910006603 | 2017-04-19 | 30 |
| 910006603 | 2017-04-21 | 30 |
| 910006603 | 2017-04-23 | 30 |
| 910006603 | 2017-04-25 | 30 |
| 910006604 | 2017-04-14 | 45 |
| 910006604 | 2017-04-18 | 45 |
With this information I must add two other columns, indicating the installment and the partial value of it, so that it results:
| pay | date | value | insta | partial|
|:-----------|------------:|:-----:|:-----:|:------:|
| 910006603 | 2017-04-19 | 30 | 1 | 30 |
| 910006603 | 2017-04-21 | 30 | 2 | 60 |
| 910006603 | 2017-04-23 | 30 | 3 | 90 |
| 910006603 | 2017-04-25 | 30 | 4 | 120 |
| 910006604 | 2017-04-14 | 45 | 1 | 45 |
| 910006604 | 2017-04-18 | 45 | 2 | 90 |
I hope to be explicit enough, thank you.
You can get it by using ROW_NUMBER() and SUM() window functions.
create table payments(pay int, dt date, value int); insert into payments values (910006603, '2017-04-19', 30), (910006603, '2017-04-21', 30), (910006603, '2017-04-23', 30), (910006603, '2017-04-25', 30), (910006604, '2017-04-14', 45), (910006604, '2017-04-18', 45);
select pay, dt as date, value, row_number() over (partition by pay order by dt) as insta, sum(value) over (partition by pay order by dt) as partial from payments;pay | date | value | insta | partial --------: | :--------- | ----: | ----: | ------: 910006603 | 2017-04-19 | 30 | 1 | 30 910006603 | 2017-04-21 | 30 | 2 | 60 910006603 | 2017-04-23 | 30 | 3 | 90 910006603 | 2017-04-25 | 30 | 4 | 120 910006604 | 2017-04-14 | 45 | 1 | 45 910006604 | 2017-04-18 | 45 | 2 | 90
dbfiddle here
Try this and let me know in case of any queries.
select pay, date, value,
(select sum(value) from t t2 where t2.pay = t.pay and t2.date <= t.date) as partial,
(select count(*) from t t3 where t3.pay = t.pay and t3.date <= t.date) as insta
from table t;