I have a transaction table like this
WITH DATA AS
(
SELECT 1 item_id, 5 quantity,
TO_DATE ('21-08-10 09:00:00', 'DD-MM-YY HH24:MI:SS') transaction_date,
100 unit_price
FROM DUAL
UNION
SELECT 2 item_id, 10 quantity,
TO_DATE ('21-08-10 09:00:00', 'DD-MM-YY HH24:MI:SS') transaction_date,
25 unit_price
FROM DUAL
UNION
SELECT 2 item_id, 10 quantity,
TO_DATE ('21-08-10 09:15:00', 'DD-MM-YY HH24:MI:SS') transaction_date,
30 unit_price
FROM DUAL
UNION
SELECT 2 item_id, 10 quantity,
TO_DATE ('21-08-10 09:30:00', 'DD-MM-YY HH24:MI:SS') transaction_date,
50 unit_price
FROM DUAL
UNION
SELECT 2 item_id, -13 quantity,
TO_DATE ('21-08-10 10:00:00', 'DD-MM-YY HH24:MI:SS') transaction_date,
50 unit_price
FROM DUAL
UNION
SELECT 1 item_id, -3 quantity,
TO_DATE ('24-08-10 09:00:00', 'DD-MM-YY HH24:MI:SS') transaction_date,
100 unit_price
FROM DUAL)
Business Logic:
+ve quantity saying incoming and -ve quantity saying outgoing
if you see the transactions for item_id 2
in 3 transactions item is coming in since quantity +ve
and one transaction item is going out since quantity -ve
if u sum all the transactions u will get the on hand quantity of a particular item 10+10+10-13 ==> 17
out of 17 quantities 7 quantity with unit price of 30 and 10 quantity with unit price of 50 remains in inventory
Its on First IN First out concept. I have mentioned the transaction date. old stocks will be pushed out soon.. what ever the quantities remains it received in last
i need to show only on hand quantity as of today for all items.
need output like this
Item ID Quantity Unit_price
1 2 100
2 7 30
2 10 50