Stock Report

[Former Member]

Hi to all,

Reporting date is 09/04/08

I want to get opening balance of any item group at the start of the month along with the received and issued quantity.

a) Month opening quantity at (01/04/08) for a Item Group(PVC Sheeting)

b) Received quantity till 09/04/08 from 01/04/08 for a item Group(PVC Sheeting)

c) Issued quantity till 09/04/08 form 01/04/08 for a item Group(PVC Sheeting)

someone plz help me out to get this query.

thanx

Regards

[Former Member]

Please try this:

SELECT T2.ItmsGrpNam, T0.ItemCode, T0.Dscription, (T1.onhand - 
SUM(T0.InQty) + SUM(T0.OutQty)) 
AS 'Opening QTY', SUM(T0.InQty) AS 'QTY Received', 
SUM(T0.OutQty) AS 'QTY Issued' 
FROM OINM T0
INNER JOIN OITM T1 ON T1.ItemCode = T0.ItemCode
INNER JOIN OITB T2 ON T2.ItmsGrpCod = T1.ItmsGrpCod
WHERE T0.DocDate >= '[%0]' AND T0.DocDate <= '[%1]' AND 
T0.InQty <> T0.OutQty AND T2.ItmsGrpNam = '[%2]'
GROUP BY T2.ItmsGrpNam, T0.ItemCode, T0.Dscription, T1.onhand

The opening balance here will only be applicable to the report up to date. It should meet most needs. If it is not suitable in your case, the query has to add some more formula to do more calculating. It will need more server resources

Thanks,

Gordon

[Former Member]

try this query

SELECT  T0.[DocDate], T0.[ItemCode], T0.[Dscription], T0.[InQty], T0.[OutQty] FROM 
OINM T0 WHERE T0.[DocDate]  between [%0] and [%1]