Dear All,
I want to create an Inventory Report with following columns. I tried the same by using OINM table
I 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