Home » SQL & PL/SQL » SQL & PL/SQL » Cuimulative totals (12c)
Cuimulative totals [message #665876] |
Thu, 28 September 2017 04:36 |
|
m.abdulhaq
Messages: 254 Registered: April 2013 Location: Ajman
|
Senior Member |
|
|
Hello experts,
i am trying to get the cumulative balances from following table with specific format.I need basically two columns one for negative amount and the other for positive amount.
create table inv_det (inv_no varchar2(12),invoice_dt date,inv_amount number);
insert into inv_det (inv_no ,invoice_dt ,inv_amount) values ( '2001',to_Date('01/09/2017','DD/MM/YYYY'),-10000);
insert into inv_det (inv_no ,invoice_dt ,inv_amount) values ( '2002',to_Date('10/09/2017','DD/MM/YYYY'),5000);
insert into inv_det (inv_no ,invoice_dt ,inv_amount) values ( '2003',to_Date('15/09/2017','DD/MM/YYYY'),6000);
insert into inv_det (inv_no ,invoice_dt ,inv_amount) values ( '2004',to_Date('15/10/2017','DD/MM/YYYY'),4000);
SELECT inv_no, invoice_dt, inv_amount,
SUM(inv_amount) OVER (ORDER BY inv_no,invoice_dt) AS RunningTotal
FROM inv_det;
INV_NO INVOICE_DT INV_AMOUNT RUNNINGTOTAL
2001 09/01/2017 00:00:00 -10,000.000 -10,000.000
2002 09/10/2017 00:00:00 5,000.000 -5,000.000
2003 09/15/2017 00:00:00 6,000.000 1,000.000
2004 10/15/2017 00:00:00 4,000.000 5,000.000
---i needed the ouput like
inv_no inv_date positive negative cummulative_balance
2001 01/09/2017 -10000 -10000
2002 10/09/2017 5000 -5000
2003 15/09/2017 6000 1000
2004 15/10/2017 4000 5000
[Updated on: Thu, 28 September 2017 04:41] Report message to a moderator
|
|
|
|
Re: Cuimulative totals [message #665878 is a reply to message #665877] |
Thu, 28 September 2017 07:55 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
To expand on what cookiemonster suggested
SELECT Inv_no,
TO_CHAR (Invoice_dt, 'MM/DD/YYYY') Invoice_dt,
Inv_amount,
CASE WHEN Inv_amount >= 0 THEN Inv_amount ELSE NULL END AS Positive,
CASE WHEN Inv_amount < 0 THEN Inv_amount ELSE NULL END AS Negative,
SUM (Inv_amount) OVER (ORDER BY Inv_no, Invoice_dt) AS Runningtotal
FROM Inv_det
ORDER BY Inv_no, Invoice_dt;
|
|
|
|
Goto Forum:
Current Time: Thu May 09 18:51:41 CDT 2024
|