Stock movement report date and warehouse wise

[Former Member]

I need this report date wise and warehouse wise...

please help ..

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,

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

--(Select i.InvntryUom from OITM i where i.ItemCode=a.Itemcode) as UOM

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

[tchidawanyika]

Hi all,

I also need this report with the Supplier Name and Catelog Number.

So on the Supplier Name I need it as a field on the report and then as a parameter to and from.

Thank you.

The report below:

Declare @FromDate Datetime

Declare @ToDate Datetime

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

a.Itemcode, a.warehouse,a.price as 'Item Cost',

max(a.Dscription) as ItemName,

sum(a.OpeningBalance) as OpeningBalance,((sum(a.OpeningBalance) *a.Price)) as 'Opening STock Value',

sum(a.INq) as 'IN',

sum(a.INq)*a.Price as 'IN Stock Value',

sum(a.OUT) as OUT,

sum(a.OUT)*a.Price as 'OUT Stock Value',

((sum(a.OpeningBalance) + sum(a.INq)) - Sum(a.OUT)) as Closing,

((sum(a.OpeningBalance) + sum(a.INq)) - Sum(a.OUT))*a.Price as 'Closing Stock Value'

--(Select i.InvntryUom from OITM i where i.ItemCode=a.Itemcode) as UOM

from( Select N1.Warehouse,

N1.Itemcode,

N1.Dscription,n1.Price,

(sum(N1.inqty)-sum(n1.outqty))as OpeningBalance, 0 as INq, 0 as OUT From dbo.OINM N1

Where N1.DocDate < @FromDate Group By N1.Warehouse,N1.ItemCode,n1.Price,

N1.Dscription

Union All

select N1.Warehouse, N1.Itemcode, N1.Dscription,n1.Price, 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 Group By N1.Warehouse,N1.ItemCode,N1.Dscription,n1.Price

Union All

select N1.Warehouse, N1.Itemcode,N1.Dscription,n1.Price, 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

Group By N1.Warehouse,N1.ItemCode,N1.Dscription,n1.Price) a, dbo.OITM I1

where a.ItemCode=I1.ItemCode

Group By a.Itemcode,a.warehouse,a.Price

Having sum(a.OpeningBalance) + sum(a.INq) + sum(a.OUT) > 0 Order By a.Itemcode