Home » SQL & PL/SQL » SQL & PL/SQL » Cuimulative totals (12c)
Cuimulative totals [message #665876] Thu, 28 September 2017 04:36 Go to next message
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 #665877 is a reply to message #665876] Thu, 28 September 2017 05:42 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
That's very easily done with CASE.
CASE WHEN inv_amount >= 0 THEN inv_amount ELSE null END as positive.
Re: Cuimulative totals [message #665878 is a reply to message #665877] Thu, 28 September 2017 07:55 Go to previous messageGo to next message
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;
Re: Cuimulative totals [message #665884 is a reply to message #665878] Thu, 28 September 2017 08:28 Go to previous message
m.abdulhaq
Messages: 254
Registered: April 2013
Location: Ajman
Senior Member
thanks Bill B
Previous Topic: Multiple record insert using PLSQL
Next Topic: regexp_replace append 0 to single digit date,month,hour,minute,second
Goto Forum:
  


Current Time: Thu Mar 28 10:50:13 CDT 2024