on 04-16-2012 8:40 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
86 | |
7 | |
6 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 | |
2 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.