Databases
StackOverflow2013
Postsdbo
POOracle SQL Query Get the remaining amount by group
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POOracle SQL Query Get the remaining amount by group
    primarykey
    data
    text
    <p>I need to find the <strong>remaining amount</strong> for each credit no. The expected result is this:</p> <pre><code>CREDIT_NO CREDIT_TYPE CREDIT_AMOUNT TOTAL_A REMAINING_AMT FINAL_TOTAL_PER_BILL A1 W 100 1000 900 600 A1 X 100 1000 800 600 A1 Y 100 1000 700 600 A1 Z 100 1000 600 600 B1 X 100 2000 1900 1700 B1 Y 100 2000 1800 1700 B1 Z 100 2000 1700 1700 </code></pre> <p>Here's the query I have done so far (please pardon the noob):</p> <pre><code>WITH TEMP AS (SELECT 1 ID, 'A1' CREDIT_NO, 'X' CREDIT_TYPE, 100 CREDIT_AMOUNT, 1000 TOTAL_A FROM DUAL UNION ALL SELECT 2, 'A1' , 'Y', 100, 1000 FROM DUAL UNION ALL SELECT 4, 'A1' , 'Z', 100, 1000 FROM DUAL UNION ALL SELECT 3, 'B1', 'X', 100, 2000 FROM DUAL UNION ALL SELECT 5, 'B1', 'Y', 100, 2000 FROM DUAL UNION ALL SELECT 6, 'B1', 'Z', 100, 2000 FROM DUAL UNION ALL SELECT 7, 'A1', 'W', 100, 1000 FROM DUAL ) SELECT TEMP1.CREDIT_NO , TEMP1.CREDIT_TYPE, TEMP1.CREDIT_AMOUNT , TEMP1.TOTAL_A , CASE WHEN TEMP1.CREDIT_NO = (LAG (TEMP1.CREDIT_NO,1) OVER (ORDER BY TEMP1.CREDIT_NO) ) -- set remaining CREDIT_AMOUNT OR (LAG (TEMP1.CREDIT_NO,1) OVER (ORDER BY TEMP1.CREDIT_NO) ) IS NULL THEN TEMP1.TOTAL_A - (SUM(TEMP1.CREDIT_AMOUNT) OVER ( ORDER BY TEMP1.CREDIT_NO ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) ) WHEN TEMP1.CREDIT_NO &lt;&gt; -- new bill, new total CREDIT_AMOUNT (LAG (TEMP1.CREDIT_NO,1) OVER (ORDER BY TEMP1.CREDIT_NO) ) THEN TEMP1.TOTAL_A - TEMP1.CREDIT_AMOUNT END AS REMAINING_AMT ,TEMP1.TOTAL_A - (SUM(TEMP1.CREDIT_AMOUNT) OVER (PARTITION BY CREDIT_NO)) AS FINAL_TOTAL_PER_BILL FROM TEMP TEMP1 ORDER BY CREDIT_NO, CREDIT_TYPE </code></pre> <p>My problem is I don't know how to compute for the remaining amount for the 2nd credit no. The result of the above query is:</p> <pre><code>CREDIT_NO CREDIT_TYPE CREDIT_AMOUNT TOTAL_A REMAINING_AMT FINAL_TOTAL_PER_BILL A1 W 100 1000 900 600 A1 X 100 1000 800 600 A1 Y 100 1000 700 600 A1 Z 100 1000 600 600 B1 X 100 2000 1900 1700 B1 Y 100 2000 1400 1700 B1 Z 100 2000 1300 1700 </code></pre> <p>Is it possible to get a running remaining amount without using a stored procedure? I tried basing it on the rownum but it is not sequential. </p> <p>Even though I have found similar questions to this (<a href="https://stackoverflow.com/questions/11454201/how-to-recursively-compute-ratio-of-remaining-amounts-based-on-rounded-values-fr">Link 1</a>, <a href="https://stackoverflow.com/questions/10547851/sql-query-to-calculate-remaining-running-balances-based-on-a-given-conditions">Link 2</a>, <a href="https://stackoverflow.com/questions/10547851/sql-query-to-calculate-remaining-running-balances-based-on-a-given-conditions">Link 3</a>) (I'm still going over the third link though), I hope you guys can help me. </p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USASC
    plurals
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POOracle SQL Query Get the remaining amount by group
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POOracle SQL Query Get the remaining amount by group
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload