on 06-24-2010 2:28 PM
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*
hi,
check following query-
Declare @FromDate Datetime
Declare @ToDate Datetime
Declare @Group nvarchar(10)
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]')
Set @Group = (Select Max(s2.ItmsGrpCod) from OITB S2 Where S2.ItmsGrpNam = '[%2]')
Select
a.Itemcode,
a.Dscription,
sum(a.[Opening Balance]) as [Opening Balance],
sum(a.[IN]) as [IN],
Sum(a.OUT) as OUT,
((sum(a.[Opening Balance]) + sum(a.[IN])) - Sum(a.OUT)) as Closing from(
Select
N1.Itemcode,
N1.Dscription,
(sum(N1.inqty)-sum(n1.outqty)) as [Opening Balance],
0 as [IN],
0 as OUT
From OINM N1
Where
N1.DocDate < @FromDate
Group By
N1.ItemCode,N1.Dscription
Union All
select
N1.Itemcode,
N1.Dscription,
0 as [Opening Balance],
sum(N1.inqty) as [IN],
0 as OUT
From OINM N1
Where
N1.DocDate >= @FromDate and N1.DocDate <= @ToDate and
N1.Inqty >0
Group By
N1.ItemCode,N1.Dscription
Union All
select
N1.Itemcode,
N1.Dscription,
0 as [Opening Balance],
0 as [IN],
sum(N1.outqty) as OUT
From OINM N1
Where
N1.DocDate >= @FromDate and N1.DocDate <=@ToDate and
N1.OutQty > 0
Group By
N1.ItemCode,N1.Dscription)a, OITM I1
where
a.ItemCode=I1.ItemCode and
I1.ItmsGrpCod = @Group
Group By
a.Itemcode,a.Dscription
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
For this you can use inventory posting report or inventory audit report.
Hope that will help you............
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
107 | |
12 | |
11 | |
6 | |
5 | |
4 | |
4 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.