Skip to Content
0
Former Member
Jun 24, 2010 at 01:28 PM

Sales and Closing Stock By Item Group

7 Views

Hi,

Can anybody give me modified query for the following code. I need Item Group wise sales & Stock. The present code is giving me all items Sales & Stock. I need it by Item Group Query and Item as separate queries.

*Start of the Query *

Declare @FromDate Datetime

Declare @ToDate Datetime

set @FromDate = (Select min(S0.Docdate) from OINM S0 where S0.Docdate >='[%0]')

set @ToDate = (Select max(S1.Docdate) from OINM S1 where S1.Docdate <='[%1]')

select * from ( SELECT T0.itemcode, min(T0.Dscription) as 'Item Description', W1.Whscode, isnull((Select sum(isnull(outqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.outqty>0 and O1.transtype in (13,15)),0) as [Sale Quantity],

(isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate<=@ToDate and O1.transtype in (59,20,16,14,19,67,-2)),0) - isnull((Select sum(isnull(outqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate<=@ToDate and O1.transtype in (21,19,60,15,67,-2,13)),0)) as [Closing Stock]

FROM OINM T0 INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode INNER JOIN OITW T2 ON T1.ItemCode = T2.ItemCode INNER JOIN OITB B1 ON T1.ItmsGrpCod=B1.ItmsGrpCod INNER JOIN OWHS W1 ON T2.WhsCode = W1.WhsCode INNER JOIN OLCT C1 ON W1.Location=C1.Code Group by T2.MinStock ,T1.itemcode, T0.Itemcode, W1.WhsCode, C1.Location) a

  • End of the Query*