In May client P003 had 600 of buy_amt (sum of sales in that period) and 1000 transfer_amt. I want to calculate the remaining limit (buy_amt - transfer_amt) but starting from top to bottom and the deduction only happens if the result is positive.
transfer_amt is ordered by day_time for date. In June client had 600 total buy_amt and 1750 transfer_amt, His total buy_amt is his starting limit (600 for June).
- First step is 600 – 1000 = – 400 (negative result, so do not deduct),
- 600 – 100 = 500 (result is positive so next deduction happens for 500 and not 600),
- next is 500 – 300 = 200 (positive) and so on.
When the month ends the last positive remaining_limit for June is transferred to July.
| date | buy_amt | transfer_amt | remaining_limit | how it should calculate |
|---|---|---|---|---|
| 30-jun-2024 | 600 | 1000 | -400 (skipped) | (600 – 1000) |
| 30-jun-2024 | 600 | 100 | 500 Yes | (600 – 100) |
| 30-jun-2024 | 600 | 300 | 200 Yes | (500 – 300) |
| 30-jun-2024 | 600 | 300 | -100 (skipped) | (200 – 300) |
| 30-jun-2024 | 600 | 50 | 150 Yes | (200 – 50) |
| … | … | … | … | … |
| 31-jul-2024 | 100 | 0 | 250 | (150 + 100) |
If the month is not finished, remaining_limit is negative and the client increases buy_amt, then the calculation starts again from the start of the month. Once a month is finished there are no re-calculations.
New contributor
Nikoloz Kalichava is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
3
You can do this with a recursive query. Here’s a commented example with results illustrating that it works. (NOTE: you would comment out the “TEST_DATA” and use your actual database table instead.)
with test_data ( client_id, txn_date, buy_amt, transfer_amt ) as
( SELECT 'P003', TO_DATE('30-JUN-2024 08:00:00','DD-MON-YYYY HH24:MI:SS'), 600, 1000 FROM DUAL UNION ALL
SELECT 'P003', TO_DATE('30-JUN-2024 09:00:00','DD-MON-YYYY HH24:MI:SS'), 600, 100 FROM DUAL UNION ALL
SELECT 'P003', TO_DATE('30-JUN-2024 10:00:00','DD-MON-YYYY HH24:MI:SS'), 600, 300 FROM DUAL UNION ALL
SELECT 'P003', TO_DATE('30-JUN-2024 11:00:00','DD-MON-YYYY HH24:MI:SS'), 600, 300 FROM DUAL UNION ALL
SELECT 'P003', TO_DATE('31-JUL-2024 08:00:00','DD-MON-YYYY HH24:MI:SS'), 600, 50 FROM DUAL ),
-- Actual solution starts here. ORDERED_DATA CTE just orders the
-- the data 1,2,3,... so that we can easily join each row to the next one
-- WITH
ordered_data ( client_id, txn_date, buy_amt, transfer_amt, rn ) as
( SELECT client_id, txn_date, buy_amt, transfer_amt, row_number() over ( partition by client_id order by txn_date ) rn
FROM test_data ),
-- REC1 CTE (named "REC1" because it's recursive) starts with the first
-- row and then recursively gets the next row(s) until it runs out of rows. Doing it recursively like this is what allows us to do calculations
-- in each row that depend on the rows selected so far, which is what
-- we need for your conditional running-total logic.
rec1 ( client_id, txn_date, buy_amt, transfer_amt, rn, remaining_limit_calc, remaining_limit_disp) AS
( SELECT r0.client_id, r0.txn_date, r0.buy_amt, r0.transfer_amt, r0.rn,
CASE WHEN r0.buy_amt - r0.transfer_amt < 0 THEN r0.buy_amt ELSE r0.buy_amt - r0.transfer_amt END remaining_limit_calc,
TO_CHAR(r0.buy_amt - r0.transfer_amt) || CASE WHEN r0.buy_amt - r0.transfer_amt < 0 THEN ' (skipped)' ELSE NULL END remaining_limit_disp
FROM ordered_data r0
WHERE rn = 1
UNION ALL
SELECT r0.client_id, r0.txn_date, r0.buy_amt, r0.transfer_amt, r1.rn + 1 rn,
CASE WHEN r1.remaining_limit_calc - r0.transfer_amt < 0 THEN r1.remaining_limit_calc ELSE r1.remaining_limit_calc - r0.transfer_amt END remaining_limit_calc,
TO_CHAR(r1.remaining_limit_calc - r0.transfer_amt) || CASE WHEN r1.remaining_limit_calc - r0.transfer_amt < 0 THEN ' (skipped)' ELSE NULL END
FROM ordered_data r0 INNER JOIN rec1 r1 ON r1.client_id = r0.client_id AND r1.rn+1 = r0.rn
)
-- This just dumps the results.
select client_id, txn_date, buy_amt, transfer_amt, remaining_limit_disp
from rec1
order by client_id, txn_date;
Results
+-----------+-----------+---------+--------------+----------------------+ | CLIENT_ID | TXN_DATE | BUY_AMT | TRANSFER_AMT | REMAINING_LIMIT_DISP | +-----------+-----------+---------+--------------+----------------------+ | P003 | 30-JUN-24 | 600 | 1000 | -400 (skipped) | +-----------+-----------+---------+--------------+----------------------+ | P003 | 30-JUN-24 | 600 | 100 | 500 | +-----------+-----------+---------+--------------+----------------------+ | P003 | 30-JUN-24 | 600 | 300 | 200 | +-----------+-----------+---------+--------------+----------------------+ | P003 | 30-JUN-24 | 600 | 300 | -100 (skipped) | +-----------+-----------+---------+--------------+----------------------+ | P003 | 31-JUL-24 | 600 | 50 | 150 | +-----------+-----------+---------+--------------+----------------------+
It’s probably no better than the MODEL solution given. I just find the syntax a little more intuitive and easy to remember.
First try with MODEL based on what I understood:
with data(client_id, dat, Buy_amt, Transfer_amt) as (
select 'P003', to_date('30-06-2024 01', 'dd-mm-yyyy hh24'), 600, 1000 union all
select 'P003', to_date('30-06-2024 02', 'dd-mm-yyyy hh24'), 600, 100 union all
select 'P003', to_date('30-06-2024 03', 'dd-mm-yyyy hh24'), 600, 300 union all
select 'P003', to_date('30-06-2024 04', 'dd-mm-yyyy hh24'), 600, 300 union all
select 'P003', to_date('30-06-2024 05', 'dd-mm-yyyy hh24'), 600, 50
)
select client_id, dat, Buy_amt, Transfer_amt, remaining_limit
from (
select row_number() over(partition by client_id order by dat) as rn,
d.* from data d
)
model
partition by (client_id)
dimension by (rn)
measures ( dat as dat, buy_amt as Buy_amt, Transfer_amt as Transfer_amt,
0 as remaining_limit, 0 as skipped, 0 as last_positive)
rules automatic order
(
remaining_limit[any] =
nvl2(skipped[cv()-1],
case when skipped[cv()-1] = 1
then nvl(last_positive[cv()-1], buy_amt[cv()]) - transfer_amt[cv()]
else
case when remaining_limit[cv()-1] < 0
then last_positive[cv()-1]
else remaining_limit[cv()-1]
end - transfer_amt[cv()]
end,
buy_amt[cv()] - transfer_amt[cv()]),
last_positive[any] = case when remaining_limit[cv()] > 0 then remaining_limit[cv()] else remaining_limit[cv()-1] end,
skipped[any] = case when remaining_limit[cv()] < 0 then 1 else 0 end
)
;
| client_id | date | buy_amt | transfer_amt | remaining_limit |
|---|---|---|---|---|
| P003 | 30/06/24 | 600 | 1000 | -400 |
| P003 | 30/06/24 | 600 | 100 | 500 |
| P003 | 30/06/24 | 600 | 300 | 200 |
| P003 | 30/06/24 | 600 | 300 | -100 |
| P003 | 30/06/24 | 600 | 50 | 150 |
This is a different aproach to the problem using MODEL clause and takeing care of new buys and months dimension. Didn’t quite sure about understanding the recalculation part within the month so this is how I got it and see the solution.
-- S a m p l e D a t a : (from question)
with tbl ( CLIENT_ID, DAT, BUY_AMT, TRANSFER_AMT ) AS
( Select 'P003', To_Date('30-06-2024 03:00:00','DD-MM-YYYY HH24:MI:SS'), 600, 1000 From Dual Union All
Select 'P003', To_Date('30-06-2024 05:00:00','DD-MM-YYYY HH24:MI:SS'), 600, 100 From Dual Union All
Select 'P003', To_Date('30-06-2024 10:00:00','DD-MM-YYYY HH24:MI:SS'), 600, 300 From Dual Union All
Select 'P003', To_Date('30-06-2024 17:00:00','DD-MM-YYYY HH24:MI:SS'), 600, 300 From Dual Union All
Select 'P003', To_Date('30-06-2024 21:00:00','DD-MM-YYYY HH24:MI:SS'), 600, 50 From Dual Union All
--
Select 'P003', To_Date('31-07-2024 06:00:00','DD-MM-YYYY HH24:MI:SS'), 100, 0 From Dual
)
-- S Q L :
Select DAT, BUY_AMT, TRANSFER_AMT, USED_AMT, REMAINING_AMT
From ( Select t.*, To_Char(DAT, 'yyyy-mm') "MM",
Min(To_Char(DAT, 'yyyy-mm')) Over(Partition By CLIENT_ID) "FROM_MM",
Max(To_Char(DAT, 'yyyy-mm')) Over(Partition By CLIENT_ID) "TO_MM",
Count(*) Over(Partition By CLIENT_ID, To_Char(DAT, 'yyyy-mm')) "MAX_RN",
Row_Number() Over(Partition By CLIENT_ID, To_Char(DAT, 'yyyy-mm') Order By DAT) "RN"
From tbl t
)
MODEL Partition By ( CLIENT_ID )
Dimension By ( MM, RN )
Measures ( DAT, BUY_AMT, TRANSFER_AMT, FROM_MM, TO_MM, MAX_RN,
0 "START_AMT", 0 "NEW_BUY_AMT", 0 "USED_AMT", 0 "REMAINING_AMT")
RULES ( NEW_BUY_AMT[ANY, ANY] = Case When TRANSFER_AMT[CV(), CV()] = 0 Then BUY_AMT[CV(), CV()] Else 0 End,
START_AMT[ANY, ANY] = BUY_AMT[CV(), 1],
USED_AMT[ANY, ANY] = Case When START_AMT[CV(), CV()] + Nvl(Sum(NEW_BUY_AMT)[CV(), RN <= CV()], 0) - Sum(USED_AMT)[CV(), RN < CV()] - TRANSFER_AMT[CV(), CV()] >= REMAINING_AMT[CV(), CV()]
Then TRANSFER_AMT[CV(), CV()]
Else 0
End,
REMAINING_AMT[ANY, ANY] = Nvl(Case When CV(RN) = 1
Then Nvl(Max(START_AMT)[MM = To_Char(ADD_MONTHS(DAT[CV(), CV()], -1), 'yyyy-mm'), RN > 0] +
Sum(NEW_BUY_AMT)[MM = To_Char(ADD_MONTHS(DAT[CV(), CV()], -1), 'yyyy-mm'), RN > 0] -
Sum(USED_AMT)[MM < CV(), RN > 0] + START_AMT[CV(), CV()], BUY_AMT[CV(), 1])
Else START_AMT[CV(), CV()] + Nvl(Sum(NEW_BUY_AMT)[CV(), RN <= CV()], 0) - Sum(USED_AMT)[CV(), RN <= CV()]
End, 0)
)
Order By CLIENT_ID, MM, RN
/* R e s u l t :
DAT BUY_AMT TRANSFER_AMT USED_AMT REMAINING_AMT
-------- ---------- ------------ ---------- -------------
30.06.24 600 1000 0 600
30.06.24 600 100 100 500
30.06.24 600 300 300 200
30.06.24 600 300 0 200
30.06.24 600 50 50 150
31.07.24 100 0 0 250 */