Report on Items validity

[former_member487237]

Hi All,

I'm looking for a report for the item validity like if the item is not used for the production 180 or more days (lying on warehouse for more than or equal to 180 days).

And should consider GRN dates, Goods Reciept Dates and as well as the opening balance to get the validity.

Help me out.

Thanks & Regards,

Saikrishna.

[former_member205766]

Hi

Try the below query

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

With Regards

Balaji Sampath

[former_member487237]

Hi Balaji Sampath,

Thanks for the reply.

I have this query but it doesn't suit for my requirement.

Can you please try any other?

Thanks & Regards,

Sai

[former_member487237]

Hi,

Any Update?

Regards,

Sai

[Former Member]

Hi,

Try this...

select b.code as 'Item Code', b.name as 'Item Description', b.Wh as 'Warehouse', b.Bal as 'Balance Qty', b.Val as 'Value',

isnull(case when b.days

isnull(case when b.days between 31 and 60 then b.bal end,0) '31-60 Days',

isnull(case when b.days between 61 and 90 then b.bal end,0) '61-90 Days',

isnull(case when b.days between 91 and 120 then b.bal end,0) '91-120 Days',

isnull(case when b.days between 121 and 150 then b.bal end,0) '121-150 Days',

isnull(case when b.days between 151 and 180 then b.bal end,0) '151-180 Days',

isnull(case when b.days >=181 then b.bal end,0) 'Above 181 Days'

from (

select a.code,a.name,a.wh,a.bal,a.val,datediff(dd,dt,getdate())'days'

from (

select max(t0.itemcode)'Code',max(t0.Dscription)'Name',

max(t0.Warehouse)'Wh',

sum(t0.inqty-t0.outqty)'Bal',sum(t0.transvalue)'Val',max(t0.docdate)'dt'

from oinm t0 inner join oitm t1 on t0.itemcode=t1.itemcode

group by t0.itemcode

)a

)b order by code

Regards,

SP Samy

[former_member487237]

Hi Swamy,

When i run the above query, I'm getting following error

1). [Microsoft][SQL Server Native Client 10.0][SQL Server]An expression of non-boolean type specified in a context where a condition is expected, near 'isnull'. 2). [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near 'b'. 3). [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement '' (SWEI) (s) could not be prepared.

Could you please check it?

Regards,

Sai

[Former Member]

select b.code as 'Item Code', b.name as 'Item Description', b.Wh

as 'Warehouse', b.Bal as 'Balance Qty', b.Val as 'Value',

isnull(case when b.days <=30 then b.bal end,0) '0-30 Days',

isnull(case when b.days between 31 and 60 then b.bal end,0) '31-60

Days',

isnull(case when b.days between 61 and 90 then b.bal end,0) '61-90

Days',

isnull(case when b.days between 91 and 120 then b.bal end,0)

'91-120 Days',

isnull(case when b.days between 121 and 150 then b.bal end,0)

'121-150 Days',

isnull(case when b.days between 151 and 180 then b.bal end,0)

'151-180 Days',

isnull(case when b.days >=181 then b.bal end,0) 'Above 181 Days'

from (

select a.code,a.name,a.wh,a.bal,a.val,datediff(dd,dt,getdate())

'days'

from (

select max(t0.itemcode)'Code',max(t0.Dscription)'Name',

max(t0.Warehouse)'Wh',

sum(t0.inqty-t0.outqty)'Bal',sum(t0.transvalue)'Val',max

(t0.docdate)'dt'

from oinm t0 inner join oitm t1 on t0.itemcode=t1.itemcode

group by t0.itemcode

)a

)b order by code

[former_member487237]

Hi samy,

Sorry for the late reply.

Your query is working fine. But not exactly as per my requirement.

For ex. I have one item called X.

for X, i have an opening balance of 100 qty which is from more than 180 days.

Now i bought again 100 qty before 2 months. and again bought 100 qty recently.

so when i run the query, it should show 100(0-30 days), 100( 60-90 days), 100(more than 180 days).

Can you please alter this query as i required.

Regards,

Sai.