cancel
Showing results for 
Search instead for 
Did you mean: 

need query which shows non moving item for last 2 years with particular warehouse??

vinayak_chavan
Participant
0 Kudos

need query which shows non moving item for last 2 years with particular warehouse??

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

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

Former Member
0 Kudos

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.

Answers (3)

Answers (3)

KennedyT21
Active Contributor
0 Kudos

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

former_member186095
Active Contributor
0 Kudos

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

vinayak_chavan
Participant
0 Kudos

not working jimmy

former_member186095
Active Contributor
0 Kudos

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

vinayak_chavan
Participant
0 Kudos

not working

vinayak_chavan
Participant
0 Kudos

using above we get receved and issued both we need only recived item

KennedyT21
Active Contributor
0 Kudos

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

vinayak_chavan
Participant
0 Kudos

above query not working

i need date and whrehouse filteration ??

KennedyT21
Active Contributor
0 Kudos

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

vinayak_chavan
Participant
0 Kudos

need item  that dosent realesd with from to date and  wharehose filteration

KennedyT21
Active Contributor
0 Kudos

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

vinayak_chavan
Participant
0 Kudos

still showing item realsed in report, query not working

KennedyT21
Active Contributor
0 Kudos

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

vinayak_chavan
Participant
0 Kudos

invalid column name whcode

KennedyT21
Active Contributor
0 Kudos

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

vinayak_chavan
Participant
0 Kudos

Dear Still item issue qty is showing , we need item that only recived not issued

KennedyT21
Active Contributor
0 Kudos

Hey These On hand refers only on stock quantity only.

Regards

Kennedy

vinayak_chavan
Participant
0 Kudos

dear we need on hand quantity , but  item that has never used in any process, only receved in wharehouse not issued yet.

KennedyT21
Active Contributor
0 Kudos

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

vinayak_chavan
Participant
0 Kudos

dear we need on hand quantity , but  item that has never used in any process, only receved in wharehouse not issued yet.

KennedyT21
Active Contributor
0 Kudos

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

vinayak_chavan
Participant
0 Kudos

yes opening balnce,

query not working

KennedyT21
Active Contributor
0 Kudos

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

vinayak_chavan
Participant
0 Kudos

i dont want issued  item.which is shown above