Dear All,
I want to create an Inventory Report with following columns. I tried the same by using OINM table
Item No. Item Name WareHouse April 2012 May 2012 June 2012 Opening Recd Issue Closing Opening Recd Issue Closing Opening Recd Issue Closing 1 ITEM 1 WH1 100 1500 1250 350 100 1500 1250 350 100 1500 1250 350 WH2 200 850 750 300 200 850 750 300 200 850 750 300 WH3 300 750 650 400 300 750 650 400 300 750 650 400 WH4 400 250 300 350 400 250 300 350 400 250 300 350 WH5 250 100 200 150 250 100 200 150 250 100 200 150 Total 1250 3450 3150 1550 1250 3450 3150 1550 1250 3450 3150 1550 2 ITEM 2 WH1 100 1500 1250 350 100 1500 1250 350 100 1500 1250 350 WH2 200 850 750 300 200 850 750 300 200 850 750 300 WH3 300 750 650 400 300 750 650 400 300 750 650 400 WH4 400 250 300 350 400 250 300 350 400 250 300 350 WH5 250 100 200 150 250 100 200 150 250 100 200 150 Total 1250 3450 3150 1550 1250 3450 3150 1550 1250 3450 3150 1550I have used following query but I am unable to get the opening balance. Kindly help me to get the desired report.
SELECT
ItemCode,
Dscription,
Case
When month(docdate) = '1' then 'January'
When month(docdate) = '2' then 'February'
When month(docdate) = '3' then 'March'
When month(docdate) = '4' then 'April'
When month(docdate) = '5' then 'May'
When month(docdate) = '6' then 'June'
When month(docdate) = '7' then 'July'
When month(docdate) = '8' then 'August'
When month(docdate) = '9' then 'September'
When month(docdate) = '10' then 'October'
When month(docdate) = '11' then 'November'
When month(docdate) = '12' then 'December'
End as [Month],
Warehouse,
sum(InQty) as [Recd],
sum(OutQty) as [Issued],
(sum(InQty)-sum(OutQty)) as [Closing]
FROM OINM
WHERE DocDate between '20120401' and '20120630'
GROUP BY month(docdate),ItemCode, Dscription,Warehouse
order by Itemcode,MONTH(docdate),Warehouse
Appreciating your help in advance.
Thank you.
Vimal Rupera