Home » SQL & PL/SQL » SQL & PL/SQL » SQL culmalative balance
SQL culmalative balance [message #190985] Sun, 03 September 2006 22:02 Go to next message
NGSOOKFONG
Messages: 16
Registered: August 2006
Junior Member

Dear sir ,

Can u please help me to write a culmalative balance. Please refer to the below scripts.

SELECT A.SEGMENT1 , A.DESCRIPTION,B.TRANSACTION_QUANTITY,
SUM(B.TRANSACTION_QUANTITY) QTYBAL B.TRANSACTION_DATE,
A.INVENTORY_ITEM_ID
FROM MTL_SYSTEM_ITEMS_FVL A, MTL_MATERIAL_TRANSACTIONS B
WHERE
A.INVENTORY_ITEM_ID = B.INVENTORY_ITEM_ID AND
A.ORGANIZATION_ID = '85' AND A.INVENTORY_ITEM_ID = 3117
AND B.TRANSACTION_DATE BETWEEN NVL(:p_date_begin, B.transaction_date) and NVL(:p_date_end, B.transaction_date

result output

12511 2 2 01-JUL-06 3117
12511 -4 -4 01-JUL-06 3117
12511 6 6 01-JUL-06 3117

result output that i need is :
12511 2 2 01-JUL-06 3117
12511 -4 -2 01-JUL-06 3117
12511 6 4 01-JUL-06 3117


Re: SQL culmalative balance [message #191004 is a reply to message #190985] Mon, 04 September 2006 01:46 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
The script:
CREATE TABLE yourtable(col1 number, col2 number, col3 date, col4 number)
/

INSERT INTO yourtable VALUES(12511, 2 , TO_DATE('01-JUL-2006','DD-MON-YYYY'), 3117);
INSERT INTO yourtable VALUES(12511, -4, TO_DATE('01-JUL-2006','DD-MON-YYYY'), 3117);
INSERT INTO yourtable VALUES(12511, 6 , TO_DATE('01-JUL-2006','DD-MON-YYYY'), 3117);

SELECT col1
     , col2
     , col3
     , col4
FROM   yourtable
/

SELECT col1
     , col2
     , SUM(col2) OVER ( ORDER BY rownum ) sumcol
     , col3
     , col4
FROM   yourtable
Order by rownum
/

DROP TABLE yourtable
/


The run:
SQL> @orafaq

Table created.


1 row created.


1 row created.


1 row created.


      COL1       COL2 COL3            COL4
---------- ---------- --------- ----------
     12511          2 01-JUL-06       3117
     12511         -4 01-JUL-06       3117
     12511          6 01-JUL-06       3117


      COL1       COL2     SUMCOL COL3            COL4
---------- ---------- ---------- --------- ----------
     12511          2          2 01-JUL-06       3117
     12511         -4         -2 01-JUL-06       3117
     12511          6          4 01-JUL-06       3117


Table dropped.
Note that I had to ORDER BY rownum because there is no deterministic row to order. You might want to change that. Have a close look at analytic functions.

MHE
Re: SQL culmalative balance [message #191017 is a reply to message #191004] Mon, 04 September 2006 02:29 Go to previous messageGo to next message
NGSOOKFONG
Messages: 16
Registered: August 2006
Junior Member

DEAR Maaher,

Thank for it, i got 1 more question to ask, pls refer to the example.



part qty date
123 1 01-Jul-06
123 -5 03-Jul-06
123 5 10-Jul-06
123 6 15-Jul-06
123 -2 17-Jul-06



Can I base on the above information (part, qty, date) to culcalate the balance qty, eg. we key in the date (10-Jul-06) to (17-Jul-06) and get the result as below.

part qty date Balance Qty
123 5 10-Jul-06 1
123 6 15-Jul-06 7
123 -2 17-Jul-06 5


Thanks

Re: SQL culmalative balance [message #191653 is a reply to message #190985] Thu, 07 September 2006 08:26 Go to previous message
venkatbollu
Messages: 53
Registered: April 2005
Location: Bangalore
Member

Hi,

SQL> select * from abc;

PAR DATE_TRAN QTY
--- --------- ----------
123 01-JUL-06 1
123 02-JUL-06 -4
123 03-JUL-06 4
123 01-AUG-06 3
123 04-AUG-06 -2


Required query:

SQL> SELECT * FROM
2 ( SELECT part, qty, date_tran, SUM(qty) OVER ( ORDER BY ROWNUM ) sumcol
3 FROM abc
4 ORDER BY ROWNUM)
5 WHERE sumcol > 0
6 /

PAR QTY DATE_TRAN SUMCOL
--- ---------- --------- ----------
123 1 01-JUL-06 1
123 4 03-JUL-06 1
123 3 01-AUG-06 4
123 -2 04-AUG-06 2


I guess its the solution you are looking for.

Thks & Rgds
Venkat.
Previous Topic: question on return function
Next Topic: rollover period
Goto Forum:
  


Current Time: Mon Dec 05 03:04:40 CST 2016

Total time taken to generate the page: 0.09656 seconds