on 08-03-2012 9:21 AM
need query which shows non moving item for last 2 years with particular warehouse??
Hi vinayak,
Try this one, hope it's working fine.....
Declare @FromDate Datetime
Declare @ToDate Datetime
Declare @Whse nvarchar(10)
select @FromDate = min(S0.Docdate) from dbo.OINM S0 where S0.Docdate >='[%0]'
select @ToDate = max(S1.Docdate) from dbo.OINM s1 where S1.Docdate <='[%1]'
select @Whse = Max(s2.Warehouse) from dbo.OINM S2 Where S2.Warehouse = '[%2]'
Select @Whse as 'Warehouse', a.Itemcode, max(a.Dscription) as ItemName,
(Select i.InvntryUom from OITM i where i.ItemCode=a.Itemcode) as UOM,
sum(a.OpeningBalance) as OpeningBalance, sum(a.INq) as 'IN', sum(a.OUT) as OUT,
((sum(a.OpeningBalance) + sum(a.INq)) - Sum(a.OUT)) as Closing
from( Select N1.Warehouse, N1.Itemcode, N1.Dscription, (sum(N1.inqty)-sum(n1.outqty))
as OpeningBalance, 0 as INq, 0 as OUT From dbo.OINM N1
Where N1.DocDate < @FromDate and N1.Warehouse = @Whse Group By N1.Warehouse,N1.ItemCode,
N1.Dscription Union All select N1.Warehouse, N1.Itemcode, N1.Dscription, 0 as OpeningBalance,
sum(N1.inqty) , 0 as OUT From dbo.OINM N1 Where N1.DocDate >= @FromDate and N1.DocDate <= @ToDate
and N1.Inqty >0 and N1.Warehouse = @Whse Group By N1.Warehouse,N1.ItemCode,N1.Dscription
Union All select N1.Warehouse, N1.Itemcode, N1.Dscription, 0 as OpeningBalance, 0 , sum(N1.outqty) as OUT
From dbo.OINM N1 Where N1.DocDate >= @FromDate and N1.DocDate <=@ToDate and N1.OutQty > 0
and N1.Warehouse = @Whse Group By N1.Warehouse,N1.ItemCode,N1.Dscription) a, dbo.OITM I1
where a.ItemCode=I1.ItemCode
Group By a.Itemcode Having sum(a.OpeningBalance) + sum(a.INq) + sum(a.OUT) > 0 Order By a.Itemcode
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
hi ganesh,
i am also trying to get a query to show me Items that are NOT selling based on Invoices.
i need something similar to the default Sales Analysis report, but to show Items that have had ZERO quantities sold on an Invoice.
Date and Warehouse Selection is needed.
any help would be great!
Thanks.
TRy This
/* select * from inv1 t1 */
DECLARE @D1 DATETIME
DECLARE @D0 DATETIME
DECLARE @D2 Nvarchar(200)
SET @D0= /* t1.DocDATE*/ '[%0]'
SET @D1= /* t1.DocDATE*/ '[%1]'
SET @D2= /* t1.Whscode*/ '[%2]'
select t1.ItemCode,Warehouse ,sum(outqty),SUM(inqty) ,onhand from OINM t1
inner join OITW t2 on t2.WhsCode=t1.Warehouse and t1.ItemCode=t2.ItemCode
where OutQty='0'
and t1.DocDate between @D0 and @d1 and Warehouse=@d2
group by t1.itemcode ,t1.Warehouse,onhand
Regards
Kennedy
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Vinayak,
Are you referring to deadstock ? (see its definition in this link:http://retail.about.com/od/retailglossaryd/g/dead-stock.htm)
You can try to use inventory posting report --> click expanded --> tick quantity released. Then you enter the period you want to know that it is possibly non moving period. See the screen capture belows:
See the green box that highlighted the criteria can be used to generate dead stock report
Rgds,
JM
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Vinayak,
You can try this query:
Select b..ItemCode, b.whscode, b.OnHand from oitw b
inner join
(select distinct a.itemcode, a.DocDate from oinm a where a.transtype = '13') T on T.itemcode = b.itemcode
where b.WhsCode between '[%0]' and '[%1]' and isnull(b.OnHand,'0') <>'0'
and t.DocDate between '[%2]' and '[%3]'
order by b.whscode
The report will shows the on hand quantity within selected period either received by grpo or good receipt document.
Good luck
JM
Hi Vinayak,
Try This
SElect ItemCode,Whscode, OnHand from oitw t1 where t1.OnHand<>'0' and ItemCode not in (
Select v1.itemcode from OINM v1 where v1.TransType='13' and v1.DocDate>GETDATE()-730 )
If this ok can write in required format
Regards
Kennedy
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
HI Vinayak,
Try This
/* select * from inv1 t1 */
DECLARE @D1 DATETIME
DECLARE @D2 Nvarchar(200)
SET @D1=/* t1.DocDATE*/ '[%1]'
SET @D2=/* t1.Whscode*/ '[%2]'
SElect ItemCode,whscode, OnHand from oitw t1 where t1.WhsCode=@d2 and t1.OnHand<>'0' and ItemCode not in (
Select v1.itemcode from OINM v1 where v1.TransType='13' and v1.DocDate>@d1-730 )
Regards
Kennedy
Hi Vinayak,
Try This,
/* select * from inv1 t1 */
DECLARE @D1 DATETIME
DECLARE @D0 DATETIME
DECLARE @D2 Nvarchar(200)
SET @D0=/* t1.DocDATE*/ '[%0]'
SET @D1=/* t1.DocDATE*/ '[%1]'
SET @D2=/* t1.Whscode*/ '[%2]'
SElect ItemCode,whscode, OnHand from oitw t1 where t1.WhsCode=@d2 and t1.OnHand<>'0' and ItemCode not in (
Select v1.itemcode from OINM v1 where v1.TransType='13' and v1.DocDate between @D0 and @d1 )
Regards
Kennedy
Try This
/* select * from inv1 t1 */
DECLARE @D1 DATETIME
DECLARE @D0 DATETIME
DECLARE @D2 Nvarchar(200)
SET @D0=/* t1.DocDATE*/ '[%0]'
SET @D1=/* t1.DocDATE*/ '[%1]'
SET @D2=/* t1.Whscode*/ '[%2]'
SElect ItemCode,whscode, OnHand from oitw t1 where t1.WhsCode=@d2 and t1.OnHand<>'0' and ItemCode not in (
Select v1.itemcode from OINM v1 where v1.TransType='13' and (v1.DocDate between @D0 and @d1) and v1.WhsCode=@d2 )
Regards
Kennedy
HI
/* select * from inv1 t1 */
DECLARE @D1 DATETIME
DECLARE @D0 DATETIME
DECLARE @D2 Nvarchar(200)
SET @D0=/* t1.DocDATE*/ '[%0]'
SET @D1=/* t1.DocDATE*/ '[%1]'
SET @D2=/* t1.Whscode*/ '[%2]'
SElect ItemCode,whscode, OnHand from oitw t1 where t1.WhsCode=@d2 and t1.OnHand<>'0' and ItemCode not in (
Select v1.itemcode from OINM v1 where v1.TransType='13' and (v1.DocDate between @D0 and @d1) and v1.Warehouse=@d2 )
Regards
Kennedy
Try This,
/* select * from inv1 t1 */
DECLARE @D1 DATETIME
DECLARE @D0 DATETIME
DECLARE @D2 Nvarchar(200)
SET @D0= /* t1.DocDATE*/ '[%0]'
SET @D1= /* t1.DocDATE*/ '[%1]'
SET @D2=/* t1.Whscode*/ '[%2]'
SElect ItemCode,whscode, OnHand from oitw t1 where t1.WhsCode=@d2
and ItemCode not in (
Select v1.itemcode from OINM v1 where v1.TransType='13' and (v1.DocDate between @D0 and @d1) and v1.Warehouse=@d2
)
Regards
Kennedy
Say the Receipt mode.
Opening balance ?????
Try This
/* select * from inv1 t1 */
DECLARE @D1 DATETIME
DECLARE @D0 DATETIME
DECLARE @D2 Nvarchar(200)
SET @D0= /* t1.DocDATE*/ '[%0]'
SET @D1= /* t1.DocDATE*/ '[%1]'
SET @D2= /* t1.Whscode*/ '[%2]'
SElect ItemCode,whscode, OnHand from oitw t1 where t1.WhsCode=@d2
and ItemCode not in (
Select v1.itemcode,v1.outqty from OINM v1 where v1.outqty='0' and (v1.DocDate between @D0 and @d1) and v1.Warehouse=@d2
)
Regards
Kennedy
Try This
/* select * from inv1 t1 */
DECLARE @D1 DATETIME
DECLARE @D0 DATETIME
DECLARE @D2 Nvarchar(200)
SET @D0= /* t1.DocDATE*/ '[%0]'
SET @D1= /* t1.DocDATE*/ '[%1]'
SET @D2= /* t1.Whscode*/ '[%2]'
SElect ItemCode,whscode, OnHand from oitw t1 where t1.WhsCode = @d2
and ItemCode not in (
Select v1.itemcode from OINM v1 where v1.TransType='310000001' and v1.outqty<>'0' and (v1.DocDate between @D0 and @d1) and v1.Warehouse = @d2
)
Regards
Kennedy
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.