on 08-11-2009 1:36 PM
Hi All ,
I have posted thread "Item Stock Report "
and got ans with following query
Code {SELECT T0.itemcode, min(T0.Dscription) as 'Item Description', min(B1.ItmsGrpNam) as 'Item Group', W1.Whscode, C1.Location, (isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate<'[%0]' and O1.transtype in (59,20,16,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<'[%0]' and O1.transtype in (21,19,60,15,67,-2,13)),0)) as 'Opening Stock', (isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>='[%0]' and O1.docdate<='[%1]' and O1.transtype in (-2)),0)- isnull((Select sum(isnull(outqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>='[%0]' and O1.docdate<='[%1]' and O1.transtype in (-2)),0)) as 'OB Quantity', isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>='[%0]' and O1.docdate<='[%1]' and O1.inqty>0 and O1.transtype in (20,18)),0) as 'Purchase Quantity', isnull((Select sum(isnull(outqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>='[%0]' and O1.docdate<='[%1]' and O1.outqty>0 and O1.transtype in (21,19)),0) as 'Purchase Return Quantity', isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>='[%0]' and O1.docdate<='[%1]' and O1.inqty>0 and O1.transtype in (16,14)),0) as 'Sale Return Quantity', isnull((Select sum(isnull(outqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>='[%0]' and O1.docdate<='[%1]' and O1.outqty>0 and O1.transtype in (13,15)),0) as 'sale Quatity', (isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate<='[%1]' 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<='[%1]' 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 where ((B1.ItmsGrpNam>='[%2]' and B1.ItmsGrpNam<='[%3]') or ('[%2]'='' and '[%3]'='')) and ((W1.Whscode>='[%4]' and W1.Whscode<='[%5]') or ('[%4]'='' and '[%5]'='')) and ((C1.Location>='[%6]' and C1.Location<='[%7]') or ('[%6]'='' and '[%7]'='')) and T0.itemcode >='[%8]' and T0.itemcode <='[%9]' Group by T1.itemcode, T0.Itemcode, W1.WhsCode, C1.Location }
Now after running this query I am getting values correctly.
When we run Inventory status report we get option "hide item without stock "
So in the same query can we put that condition .
What i means is If
ItemOpening Stock = Item Purchase Qty = Item Purchase Return Qty = Item Sales Qty = Item Sales Return Quantity= Closing Stock = 0
Then do not display those Item in report
I am expecting reply from Suraj and Gordan
Try this !
select * from (
SELECT T0.itemcode, min(T0.Dscription) as 'Item Description', min(B1.ItmsGrpNam) as 'Item Group', W1.Whscode, C1.Location, (isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate<'[%0]' and O1.transtype in (59,20,16,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<'[%0]' and O1.transtype in (21,19,60,15,67,-2,13)),0)) as [Opening Stock],
(isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>='[%0]' and O1.docdate<='[%1]' and O1.transtype in (-2)),0)- isnull((Select sum(isnull(outqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>='[%0]' and O1.docdate<='[%1]' and O1.transtype in (-2)),0)) as [OB Quantity],
isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>='[%0]' and O1.docdate<='[%1]' and O1.inqty>0 and O1.transtype in (20,18)),0) as [Purchase Quantity],
isnull((Select sum(isnull(outqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>='[%0]' and O1.docdate<='[%1]' and O1.outqty>0 and O1.transtype in (21,19)),0) as [Purchase Return Quantity], isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>='[%0]' and O1.docdate<='[%1]' and O1.inqty>0 and O1.transtype in (16,14)),0) as [Sale Return Quantity], isnull((Select sum(isnull(outqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>='[%0]' and O1.docdate<='[%1]' and O1.outqty>0 and O1.transtype in (13,15)),0) as [sale Quatity], (isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate<='[%1]' 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<='[%1]' 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 where ((B1.ItmsGrpNam>='[%2]' and B1.ItmsGrpNam<='[%3]') or ('[%2]'='' and '[%3]'='')) and ((W1.Whscode>='[%4]' and W1.Whscode<='[%5]') or ('[%4]'='' and '[%5]'='')) and ((C1.Location>='[%6]' and C1.Location<='[%7]') or ('[%6]'='' and '[%7]'='')) and T0.itemcode >='[%8]' and T0.itemcode <='[%9]' Group by T1.itemcode, T0.Itemcode, W1.WhsCode, C1.Location) a
where
(a.[Opening Stock]+a.[OB Quantity]+a.[Purchase Quantity]+a.[Purchase Return Quantity]+a.[Sale Return Quantity]+a.[sale Quatity]) !=0
Try this ! !
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Declare @FromDate Datetime
Declare @ToDate Datetime
Declare @ItmsGrpNam Declare @FromDate Datetime
Declare @ToDate Datetime
Declare @ItmsGrpNam nvarchar(50)
Declare @Whscode nvarchar(8)
Declare @Location nvarchar(50)
Declare @itemcode nvarchar(50)
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 @ItmsGrpNam = (select max(S2.ItmsGrpNam) from OITB S2 where S2.ItmsGrpNam='[%2]')
set @Whscode = (Select max(s3.Whscode) from OWHS s3 where s3.whscode = '[%3]')
set @Location = (select max(s4.Location) from OLCT s4 where s4.Location ='[%4]')
set @itemcode = (Select max(S5.ItemCode) from OINM S5 Where S5.ItemCode ='[%5]')
select * from ( SELECT T0.itemcode, min(T0.Dscription) as 'Item Description', min(B1.ItmsGrpNam) as 'Item Group',
W1.Whscode, C1.Location, (isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and
O1.Warehouse=W1.Whscode and O1.docdate<@FromDate and O1.transtype in (59,20,16,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<@FromDate and O1.transtype in (21,19,60,15,67,-2,13)),0)) as [Opening Stock], (isnull((Select
sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and
O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.transtype in (-2)),0)- 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.transtype in (-2)),0)) as [OB Quantity], isnull((Select sum(isnull(inqty,0)) from OINM O1
where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and
O1.inqty>0 and O1.transtype in (20,18)),0) as [Purchase Quantity], 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 (21,19)),0) as [Purchase Return Quantity], isnull((Select sum(isnull(inqty,0)) from
OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate
and O1.inqty>0 and O1.transtype in (16,14)),0) as [Sale Return Quantity], 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 Quatity], (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
where (B1.ItmsGrpNam>=@ItmsGrpNam and B1.ItmsGrpNam<=@ItmsGrpNam) and (W1.Whscode>=@Whscode and
W1.Whscode<=@Whscode) and (C1.Location>=@Location and C1.Location<=@Location) and T0.itemcode >=@itemcode and
T0.itemcode <=@itemcode
Group by T1.itemcode, T0.Itemcode, W1.WhsCode, C1.Location) a where (a.[Opening Stock]a.[OB Quantity]a.[Purchase
Quantity]a.[Purchase Return Quantity]a.[Sale Return Quantity]+a.[sale Quatity]) !=0
hi suraj,
Dont be sorry!!!!!!!!! , back to the point
Result after running this query ,
some of Report selection parameters cannot be defined in range criteria , means i can select only one item group , one warehouse and one item there is no range selection in this, also after running this query MSSQL error is coming.
Error Defination. "Incorrect Syntax Near Keyword declare , the variable name @ from date has already been defined variable name should be unique for query batch or stored"
Declare @FromDate Datetime
Declare @ToDate Datetime
Declare @ItmsGrpNam nvarchar(50)
Declare @ItmsGrpNam1 nvarchar(50)
Declare @Whscode nvarchar(8)
Declare @Whscode1 nvarchar(8)
Declare @Location nvarchar(50)
Declare @Location1 nvarchar(50)
Declare @itemcode nvarchar(50)
Declare @itemcode1 nvarchar(50)
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 @ItmsGrpNam = (select max(S2.ItmsGrpNam) from OITB S2 where S2.ItmsGrpNam ='[%2]')
set @ItmsGrpNam1 = (select max(S22.ItmsGrpNam) from OITB S22 where S22.ItmsGrpNam ='[%3]')
set @Whscode = (Select max(s3.Whscode) from OWHS s3 where s3.whscode = '[%4]')
set @Whscode1 = (Select max(s33.Whscode) from OWHS s33 where s33.whscode = '[%5]')
set @Location = (select max(s4.Location) from OLCT s4 where s4.Location ='[%6]')
set @Location1 = (select max(s44.Location) from OLCT s44 where s44.Location ='[%7]')
set @itemcode = (Select max(S5.ItemCode) from OINM S5 Where S5.ItemCode ='[%8]')
set @itemcode1 = (Select max(S55.ItemCode) from OINM S55 Where S55.ItemCode ='[%9]')
select * from ( SELECT T0.itemcode, min(T0.Dscription) as 'Item Description', min(B1.ItmsGrpNam) as 'Item Group', W1.Whscode, C1.Location, (isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate<@FromDate and O1.transtype in (59,20,16,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<@FromDate and O1.transtype in (21,19,60,15,67,-2,13)),0)) as [Opening Stock], (isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.transtype in (-2)),0)- 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.transtype in (-2)),0)) as [OB Quantity], isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.inqty>0 and O1.transtype in (20,18)),0) as [Purchase Quantity], 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 (21,19)),0) as [Purchase Return Quantity], isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.inqty>0 and O1.transtype in (16,14)),0) as [Sale Return Quantity], 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 Quatity], (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
where (B1.ItmsGrpNam>=@ItmsGrpNam and B1.ItmsGrpNam<=@ItmsGrpNam1) and (W1.Whscode>=@Whscode and W1.Whscode<=@Whscode1) and (C1.Location>=@Location and C1.Location<=@Location1) and T0.itemcode >=@itemcode and T0.itemcode <=@itemcode1
Group by T1.itemcode, T0.Itemcode, W1.WhsCode, C1.Location) a where (a.[Opening Stock]a.[OB Quantity]a.[Purchase Quantity]+
a.[Purchase Return Quantity]a.[Sale Return Quantity]a.[sale Quatity]) !=0
Got the result
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
hi
In the same query.
Instead of Selecting posting period from this date to other date
can we provide directly month/week wise selection for generation of that query
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi!
It is possible, but it need UDT,UDF's.
It is quite difficult.
On the above report itself, user can directly type the Date on the Text Box instead selecting from the list.
i mean type it
From Date - 01/07/09
To Date - 31/07/09
Query is wrote in such away it will pick only the existing from and todate from the table within the specified range.
hi Suraj
Pls check the query {Declare @FromDate Datetime Declare @ToDate Datetime Declare @ItmsGrpNam nvarchar(50) Declare @ItmsGrpNam1 nvarchar(50) Declare @Whscode nvarchar(8) Declare @Whscode1 nvarchar(8) Declare @Location nvarchar(50) Declare @Location1 nvarchar(50) Declare @itemcode nvarchar(50) Declare @itemcode1 nvarchar(50) 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 @ItmsGrpNam = (select max(S2.ItmsGrpNam) from OITB S2 where S2.ItmsGrpNam ='[%2]') set @ItmsGrpNam1 = (select max(S22.ItmsGrpNam) from OITB S22 where S22.ItmsGrpNam ='[%3]') set @Whscode = (Select max(s3.Whscode) from OWHS s3 where s3.whscode = '[%4]') set @Whscode1 = (Select max(s33.Whscode) from OWHS s33 where s33.whscode = '[%5]') set @Location = (select max(s4.Location) from OLCT s4 where s4.Location ='[%6]') set @Location1 = (select max(s44.Location) from OLCT s44 where s44.Location ='[%7]') set @itemcode = (Select max(S5.ItemCode) from OINM S5 Where S5.ItemCode ='[%8]') set @itemcode1 = (Select max(S55.ItemCode) from OINM S55 Where S55.ItemCode ='[%9]') select * from ( SELECT T0.itemcode, min(T0.Dscription) as 'Item Description', min(B1.ItmsGrpNam) as 'Item Group', W1.Whscode, C1.Location, (isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate<@FromDate and O1.transtype in (59,20,16,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<@FromDate and O1.transtype in (21,19,60,15,67,-2,13)),0)) as [Opening Stock], (isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.transtype in (-2)),0)- 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.transtype in (-2)),0)) as [OB Quantity], isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.inqty>0 and O1.transtype in (20,18)),0) as [Purchase Quantity], 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 (21,19)),0) as [Purchase Return Quantity], isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.inqty>0 and O1.transtype in (16,14)),0) as [Sale Return Quantity], 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 Quatity], (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 where (B1.ItmsGrpNam>=@ItmsGrpNam and B1.ItmsGrpNam<=@ItmsGrpNam1) and (W1.Whscode>=@Whscode and W1.Whscode<=@Whscode1) and (C1.Location>=@Location and C1.Location<=@Location1) and T0.itemcode >=@itemcode and T0.itemcode <=@itemcode1 Group by T1.itemcode, T0.Itemcode, W1.WhsCode, C1.Location) a where (a.[Opening Stock]a.[OB Quantity]a.[Purchase Quantity]+ a.[Purchase Return Quantity]a.[Sale Return Quantity]a.[sale Quatity]) !=0}
There are lot of selection criteria which user has to select while generating the report.For the same result I need two queries*** 1) With only Selection of date range and warehouse . ****2) With only selection of date range only **************************Pls do not show item without stock that is keep this (a.[Opening Stock]a.[OB Quantity]a.[Purchase Quantity]+ a.[Purchase Return Quantity]a.[Sale Return Quantity]a.[sale Quatity]) !=0) condition as it is
Declare @FromDate Datetime
Declare @ToDate Datetime
Declare @Whscode nvarchar(8)
Declare @Whscode1 nvarchar(8)
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 @Whscode = (Select max(s3.Whscode) from OWHS s3 where s3.whscode = '[%4]')
set @Whscode1 = (Select max(s33.Whscode) from OWHS s33 where s33.whscode = '[%5]')
select * from
( SELECT T0.itemcode, min(T0.Dscription) as 'Item Description', min(B1.ItmsGrpNam) as 'Item Group', W1.Whscode, C1.Location,
(isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate<@FromDate and O1.transtype in (59,20,16,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<@FromDate and O1.transtype in (21,19,60,15,67,-2,13)),0)) as [Opening Stock],
(isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.transtype in (-2)),0)- 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.transtype in (-2)),0)) as [OB Quantity],
isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.inqty>0 and O1.transtype in (20,18)),0) as [Purchase Quantity], 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 (21,19)),0) as [Purchase Return Quantity],
isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.inqty>0 and O1.transtype in (16,14)),0) as [Sale Return Quantity],
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 Quatity], (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
where
(W1.Whscode>=@Whscode and W1.Whscode<=@Whscode1)
Group by T1.itemcode, T0.Itemcode, W1.WhsCode, C1.Location) a where (a.[Opening Stock]a.[OB Quantity]a.[Purchase Quantity]+ a.[Purchase Return Quantity]a.[Sale Return Quantity]a.[sale Quatity]) !=0
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', min(B1.ItmsGrpNam) as 'Item Group', W1.Whscode, C1.Location,
(isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate<@FromDate and O1.transtype in (59,20,16,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<@FromDate and O1.transtype in (21,19,60,15,67,-2,13)),0)) as [Opening Stock],
(isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.transtype in (-2)),0)- 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.transtype in (-2)),0)) as [OB Quantity],
isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.inqty>0 and O1.transtype in (20,18)),0) as [Purchase Quantity], 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 (21,19)),0) as [Purchase Return Quantity],
isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.inqty>0 and O1.transtype in (16,14)),0) as [Sale Return Quantity],
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 Quatity], (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 T1.itemcode, T0.Itemcode, W1.WhsCode, C1.Location) a where (a.[Opening Stock]a.[OB Quantity]a.[Purchase Quantity]+ a.[Purchase Return Quantity]a.[Sale Return Quantity]a.[sale Quatity]) !=0
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 *,a.[Closing Stock]-a.[Opening Stock] from ( SELECT T0.itemcode, min(T0.Dscription) as 'Item Description',
min(B1.ItmsGrpNam) as 'Item Group', W1.Whscode, C1.Location,
(isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate<@FromDate and O1.transtype in (59,20,16,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<@FromDate and O1.transtype in (21,19,60,15,67,-2,13)),0)) as [Opening Stock],
(isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.transtype in (-2)),0)- 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.transtype in (-2)),0)) as [OB Quantity],
isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.inqty>0 and O1.transtype in (20,18)),0) as [Purchase Quantity],
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 (21,19)),0) as [Purchase Return Quantity],
isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.inqty>0 and O1.transtype in (16,14)),0) as [Sale Return Quantity],
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 Quatity], (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 T1.itemcode, T0.Itemcode, W1.WhsCode, C1.Location) a
where (a.[Opening Stock]+a.[OB Quantity]+a.[Purchase Quantity]+ a.[Purchase Return Quantity]+a.[Sale Return Quantity]+a.[sale Quatity]) !=0
Hi Suraj ,
Thankx you have not Named the last coloumn of this report Last Column(which is Opening Stock- Closing Stock ) should be name as Reorder Qty.
Also if you do remember about our report generation criteria
i) Date range - Which can be met wtih the above report
ii) Date range with warehouse selection (pls post query for this with Last Column(which is Opening Stock- Closing Stock ))
iii) Details selection from Item group to warehouse to date range to item level.
i have got one query which satisfy first criteria and pls post queries in seperate reply
also do not forgot two condition .
i) hiding item with no stock i.e opening stock = purchaseqty= sales delivery stock= closingstock=0(zero)
ii)last column which is name as Reorder Value which is subtraction of closing stock - opening stock
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 *,a.[Closing Stock]-a.[Opening Stock] as [Reorder Qty] from ( SELECT T0.itemcode, min(T0.Dscription) as 'Item Description', min(B1.ItmsGrpNam) as 'Item Group', W1.Whscode, C1.Location, (isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate<@FromDate and O1.transtype in (59,20,16,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<@FromDate and O1.transtype in (21,19,60,15,67,-2,13)),0)) as [Opening Stock], (isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.transtype in (-2)),0)- 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.transtype in (-2)),0)) as [OB Quantity], isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.inqty>0 and O1.transtype in (20,18)),0) as [Purchase Quantity], 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 (21,19)),0) as [Purchase Return Quantity], isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.inqty>0 and O1.transtype in (16,14)),0) as [Sale Return Quantity], 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 Quatity], (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 T1.itemcode, T0.Itemcode, W1.WhsCode, C1.Location) a where (a.[Opening Stock]a.[OB Quantity]a.[Purchase Quantity]+ a.[Purchase Return Quantity]a.[Sale Return Quantity]a.[sale Quatity]) !=0
Declare @FromDate Datetime Declare @ToDate Datetime Declare @Whscode nvarchar(8) Declare @Whscode1 nvarchar(8) 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 @Whscode = (Select max(s3.Whscode) from OWHS s3 where s3.whscode = '[%4]') set @Whscode1 = (Select max(s33.Whscode) from OWHS s33 where s33.whscode = '[%5]') select *,a.[Opening Stock]-a.[Closing Stock] as [Reorder Qty] from ( SELECT T0.itemcode, min(T0.Dscription) as 'Item Description', min(B1.ItmsGrpNam) as 'Item Group', W1.Whscode, C1.Location, (isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate<@FromDate and O1.transtype in (59,20,16,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<@FromDate and O1.transtype in (21,19,60,15,67,-2,13)),0)) as [Opening Stock], (isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.transtype in (-2)),0)- 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.transtype in (-2)),0)) as [OB Quantity], isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.inqty>0 and O1.transtype in (20,18)),0) as [Purchase Quantity], 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 (21,19)),0) as [Purchase Return Quantity], isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.inqty>0 and O1.transtype in (16,14)),0) as [Sale Return Quantity], 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 Quatity], (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 where (W1.Whscode>=@Whscode and W1.Whscode<=@Whscode1) Group by T1.itemcode, T0.Itemcode, W1.WhsCode, C1.Location) a where (a.[Opening Stock]a.[OB Quantity]a.[Purchase Quantity]+ a.[Purchase Return Quantity]a.[Sale Return Quantity]a.[sale Quatity]) !=0
hi,
This query is not fetching data from some warehouse i.e its excluding some warehouse.
Also does we consider inventory transfer in this query so if we then we need to add one more column called inventory transfer so that we can tally the closing stock quantity
or else sale quantity , sale return quantity and purchase return quantity and purchase quantity should be shown warehouse wise
For the same query if we do want to know what is consumption from that warehouse
how do we get it also can u add one field which is from OITM.Minlevel to the following query with consumption quantity
Code {
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 *,([Opening Stock]+ [OB Quantity] +[Purchase Quantity] + [Sale Return Quantity] - [Closing Stock]) as[Consumption Quantity] from ( SELECT T0.itemcode, min(T0.Dscription) as 'Item Description', min(B1.ItmsGrpNam) as 'Item Group', W1.Whscode, (isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate<@FromDate and O1.transtype in (59,20,16,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<@FromDate and O1.transtype in (21,19,60,15,67,-2,13)),0)) as [Opening Stock], (isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.transtype in (-2)),0)- 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.transtype in (-2)),0)) as [OB Quantity], isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.inqty>0 and O1.transtype in (20,18)),0) as [Purchase Quantity], 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 (21,19)),0) as [Purchase Return Quantity], isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.inqty>0 and O1.transtype in (16,14)),0) as [Sale Return Quantity], 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 T1.itemcode, T0.Itemcode, W1.WhsCode, C1.Location) a where (a.[Opening Stock]a.[OB Quantity]a.[Purchase Quantity]+ a.[Purchase Return Quantity]a.[Sale Return Quantity]a.[sale quantity]) !=0 and whscode <> 'ACER'}
Hi Suraj,
Its Running successfully now
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
91 | |
8 | |
7 | |
4 | |
4 | |
3 | |
3 | |
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.