cancel
Showing results for 
Search instead for 
Did you mean: 

Opening and Closing Stock with Values

Former Member
0 Kudos

Dear Experts,

                    Can anyone help me to find out Opening Stock and Closing Stock qty with Values. The format required is,

1.Opening Stock(Qty) 2.Opening Stock (Value) 3.Received Qty 4.Received Value 5.Issued Qty 6.Issued Value 7.Closing Stock(Qty) 8.Closing Stock (Value)

Thanks in Advance,

Bhoopathi.K

Accepted Solutions (1)

Accepted Solutions (1)

former_member205766
Active Contributor
0 Kudos

Hi Bhoopathi

Try this I think it may help you

Declare @FromDate Datetime

Declare @ToDate Datetime

Declare @ItmsGrpNam varchar(100)

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 @ItmsGrpNam = max(s2.ItmsGrpNam) from dbo.OITB S2 Where S2.ItmsGrpNam ='[%2]'

select @Whse = Max(s3.Warehouse) from dbo.OINM S3 Where S3.Warehouse = '[%3]'

Select  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 ,

(Select i.LstEvlPric from OITM i where i.ItemCode=a.Itemcode) as LastRate,

(((sum(a.OpeningBalance) + sum(a.INq)) - Sum(a.OUT))*(Select i.LstEvlPric from OITM i where i.ItemCode=a.Itemcode)) as 'Closing Value',

(Select i.LastPurDat from OITM i where i.ItemCode=a.Itemcode) as 'Last Purchase Date',

(Select i.LstEvlDate from OITM i where i.ItemCode=a.Itemcode) as 'Last Issue Date',

(Select i.ItmsGrpCod from OITM i where i.ItemCode=a.Itemcode) as 'Group code',

(Select b.ItmsGrpNam from OITB b where  b.ItmsGrpCod = I1.ItmsGrpCod) as 'Group Name'

from( Select  N1.Itemcode, N1.Dscription, (sum(N1.inqty)-sum(n1.outqty))

as OpeningBalance, 0 as INq, 0 as OUT From dbo.OINM N1 inner join OITM i on i.ItemCode = N1.ItemCode

inner join OITB b on b.ItmsGrpCod = i.ItmsGrpCod

Where N1.DocDate < @FromDate and N1.Warehouse = @Whse and  b.ItmsGrpNam = @ItmsGrpNam

Group By N1.ItemCode,

N1.Dscription Union All select N1.Itemcode, N1.Dscription, 0 as OpeningBalance,

sum(N1.inqty) , 0 as OUT From dbo.OINM N1 inner join OITM i on i.ItemCode = N1.ItemCode

inner join OITB b on b.ItmsGrpCod = i.ItmsGrpCod

Where N1.DocDate >= @FromDate and N1.DocDate <= @ToDate

and N1.Inqty >0  and N1.Warehouse = @Whse and  b.ItmsGrpNam = @ItmsGrpNam Group By N1.ItemCode,N1.Dscription

Union All select  N1.Itemcode, N1.Dscription, 0 as OpeningBalance, 0 , sum(N1.outqty) as OUT

From dbo.OINM N1 inner join OITM i on i.ItemCode = N1.ItemCode

inner join OITB b on b.ItmsGrpCod = i.ItmsGrpCod

Where N1.DocDate >= @FromDate

and N1.DocDate <=@ToDate and N1.OutQty > 0 and N1.Warehouse = @Whse and  b.ItmsGrpNam = @ItmsGrpNam

Group By N1.ItemCode,N1.Dscription) a, dbo.OITM I1 ,OITB b1

where a.ItemCode=I1.ItemCode

and I1.ItmsGrpCod = b1.ItmsGrpCod

Group By a.Itemcode ,I1.ItmsGrpCod , b1.ItmsGrpNam  Having sum(a.OpeningBalance) + sum(a.INq) + sum(a.OUT) > 0 Order By a.Itemcode

Regards

Balaji Sampath

Former Member
0 Kudos

Dear Balaji,

                   It is very helpfull to me  and can i get the value for the Opening Balance Qty.

Regards,

Bhoopathi.K

Answers (0)