Skip to Content
0
Former Member
Nov 09, 2012 at 11:32 AM

Inventory Report with Opening Balance

53 Views

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