OraFAQ Forum: SQL & PL/SQL » cost of balance items recently purchased

Home » SQL & PL/SQL » SQL & PL/SQL » cost of balance items recently purchased (oracle 9i, XP)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

cost of balance items recently purchased [message #315971]

Wed, 23 April 2008 06:43

nasir_mughal
Messages: 122
Registered: April 2007
Location: Karachi

Senior Member

I have these four tables

SQL> select * from items;

ITEM_ID ITEM_LABEL
--- ----------
001 9122DS
002 9144DS
003 9166DS

SQL> SELECT * FROM STOCK;

ITEM_ID OP_QTY OP_UNIT_COST
--- ---------- ------------
001 6 12000
003 3 15000

SQL> SELECT * FROM PURCHASE;

VOUCHER V_DATE ITEM_ID QTY RATE
------- ------- --- -------- -------
1 01-APR-08 001 2 12500
2 06-APR-08 003 1 15000
3 07-APR-08 002 2 14000
4 10-APR-08 001 3 13500
5 12-APR-08 001 1 13600
6 15-APR-08 003 5 15200

SQL> SELECT * FROM SALE;

VOUCHER V_DATE ITEM_ID QTY RATE
-- ------- ------- -------- ----------
1 17-APR-08 001 2 16000
2 18-APR-08 003 1 17000
3 18-APR-08 001 1 16500
4 18-APR-08 003 1 17200
5 21-APR-08 002 1 15000
6 23-APR-08 001 4 16500

i want to get

1. balance stock item wise
2. cost of balance items most recently purchased

From this query I can get balance stock but how can I get cost these items.

SQL> SELECT A.ITEM_LABEL,NVL(B.OP_QTY,0)+NVL(C.IN_QTY,0)- NVL(D.OUT_QTY,0) BALANCE_QTY
FROM ITEMS A, STOCK B , (SELECT ITEM_ID,SUM(QTY) IN_QTY FROM PURCHASE GROUP BY ITEM_ID) C,
(SELECT ITEM_ID,SUM(QTY) OUT_QTY FROM SALE GROUP BY ITEM_ID) D
WHERE A.ITEM_ID=B.ITEM_ID(+) AND A.ITEM_ID=C.ITEM_ID(+) AND A.ITEM_ID=D.ITEM_ID(+)

ITEM_LABEL BALANCE_QTY
---------- -----------
9122DS 5
9144DS 1
9166DS 7

My query should return

ITEM_LABEL BALANCE_QTY BALANCE_ITEMS_COST
---------- ---------- -------------------
9122DS 5 79100
9144DS 1 14000
9166DS 7 106000 ( e.g: from bottom to top 7 "9166DS" in purchase table if not enough in purchase table then opening stock value)

please help me

Thanx in advance

[Updated on: Wed, 23 April 2008 06:46]

Report message to a moderator