cancel
Showing results for 
Search instead for 
Did you mean: 

Stock Statement

Former Member
0 Kudos

Our requirment is for simple stock statement query

which will generate stock statement warehouse wise for specific period

It should contain

========================================================================================================

Part No | Description | Warehouse | Op. Qty | Op. Value | Received Qty | Received Value| Issued Qty | Issued Value | Closing Qty | Closing Value

========================================================================================================

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

Hi,

Pl check the following query -

Declare @FromDate Datetime

Declare @ToDate Datetime

Declare @Whse nvarchar(10)

Set @FromDate = (Select min(S0.Docdate) from dbo.OINM S0 where S0.Docdate >='[%0]')

Set @ToDate = (Select max(S1.Docdate) from dbo.OINM s1 where S1.Docdate <='[%1]')

Set @Whse = (Select 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

Regards

Sandeep

former_member325312
Active Contributor
0 Kudos

Hi Mahadev

find the query for stock report

Select X.DocDate, X.ItemCode,

((Select isnull(sum(InQty-OutQty),'0')From OINM T0 Where T0.ItemCode=X.ItemCode And

(T0.DataSource='I' or T0.DataSource='N') And T0.DocDate < X.DocDate)) As [OpeningStock],

IsNull(sum(X.InQty),0)As[Received Qty],IsNull(sum(X.OutQty),0)As[Consumed Qty],

((Select isnull(sum(InQty-OutQty),'0')From OINM T0 Where T0.ItemCode=X.ItemCode And

(T0.DataSource='I' or T0.DataSource='N') And T0.DocDate < X.DocDate)+

(Select isNULL(sum(T0.InQty),'0')From OINM T0 Where T0.ItemCode=X.ItemCode And T0.DocDate<=X.DocDate And

(T0.DataSource='I' or T0.DataSource='N') And T0.DocDate = X.DocDate)-

(Select isNULL(sum(T0.OutQty),'0')From OINM T0 Where T0.ItemCode=X.ItemCode And T0.DocDate<=X.DocDate And

(T0.DataSource='I' or T0.DataSource='N') And T0.DocDate = X.DocDate))As [ClosingStock]

FROM OINM X Where ItemCode=X.ItemCode And

(X.DataSource='I' or X.DataSource='N')

Group by X.DocDate,X.ItemCode

Order by X.DocDate

Regards

Jenny