on 04-05-2017 11:09 AM
Hi All,
The below query is working fine. Now i want to add two more fields in this query.
1. Stock value for Opening,IN,OUT and Closing stock
2. Vendor Name from Last purchase.
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 a.Itemcode, max(a.Dscription) as ItemName,@Whse as 'Warehouse', 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
Cheers!
User | Count |
---|---|
94 | |
11 | |
11 | |
6 | |
6 | |
4 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.