Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Opening and Closing Stock Monthwise

ADFBeginerJul 14 2011 — edited Jul 20 2011
hi,

I need to display month wise Stock details like below

month-year Opening-Stock STock-In Stock-Out closing-Stock


I have made the query, but this query gives only monthwise stock-in and stock-out. How do i get Opening-Stock and closing-Stock.


SELECT e.STORE, months.month,
SUM( (CASE WHEN trunc(e.MAT_IN_DATE,'MM') = months.month THEN MVALUE ELSE 0 END) )
Mat_Rcvd,
SUM( (CASE WHEN trunc(E.MAT_OUT_DATE,'MM') = months.month THEN MVALUE ELSE 0 END) ) Mat_Out
FROM
(
SELECT STORE,ITEM_CODE, SUM(NVL(MQTY,0)) M_QTY, SUM(NVL(MVALUE,0)) MVALUE,
MAT_OUT_DATE, MAT_IN_DATE
FROM
(
SELECT GD.TO_STORE STORE , GD.ITEM_CODE,
GD.QUANTITY MQTY, GD.AED_RATE * GD.QUANTITY MVALUE, NULL MAT_OUT_DATE , GRN_DATE MAT_IN_DATE
FROM GRN_DETAILS GD , GRN_MASTER GM
WHERE GD.GRN_NO = GM.GRN_NO
UNION ALL
SELECT MI.STORE, MI.ITEM_CODE, (NVL(MI.QUANTITY,0)) MQTY,
MI.QUANTITY * UNIT_RATE MVALUE , mi.ISSUE_DATE MAT_OUT_DATE , NULL MAT_IN_DATE
FROM MATERIAL_ISSUE MI , MINR_MASTER MM
WHERE MI.MIN_NO = MM.MIN_NO
)
WHERE STORE='ABC'
GROUP BY STORE, ITEM_CODE, MAT_OUT_DATE, MAT_IN_DATE
) E ,
(SELECT add_months( date '2007-07-01', level ) month,
FROM dual
WHERE add_months( date '2007-07-01', level ) < SYSDATE
CONNECT BY level <= 30 )months
GROUP BY e.STORE, months.month
ORDER BY MONTH
/
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 17 2011
Added on Jul 14 2011
5 comments
1,556 views