CODEWITHSUNDEEP
oracle-databasecursorsum
Rejith Krishnan
Rejith Krishnan

Reputation: 127

Query to find row till which sum less than an amount

I have an account where interest is debited corresponding to each account as below

amount      Date
2           01-01-2012
5           02-01-2012
2           05-01-2012
1           07-01-2012

If the total credit in the account is 8. Ineed a query to find till what dates interest the credit amount can adjust.

Here the query should give output as 02-01-2012(2+5 < 8). I know this can be handled through cursor. But is there any method to write this as a single query in ORACLE.

Upvotes: 2

Views: 1625

Answers (3)

Jens Schauder
Jens Schauder

Reputation: 81988

Doesn't do the (2+5)<8 thing yet:

select max(cum_sum), max(date)
from (
    select date, 
        sum(amount) over (order by date) cum_sum
) where cum_sum < 8

Upvotes: 0

Quassnoi
Quassnoi

Reputation: 425813

SELECT  pdate
FROM    (
        SELECT  t.*,
                LAG(date) OVER (ORDER BY date) AS pdate
                8 - SUM(amount) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS diff
        FROM    mytable t
        ORDER BY
                date
        )
WHERE   diff < 0
        AND rownum = 1

Upvotes: 3

Julia McGuigan
Julia McGuigan

Reputation: 654

Not knowing the structure of your table, here's a guess:

SELECT date from your_table
GROUP BY AMOUNT
HAVING SUM(AMOUNT) < 8

Note: this is LESS THAN 8. Change the conditional as appropriate.

Upvotes: 1

Related Questions