Hi, use this query to get the item closing stock.
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
Hi,
I have removed the date parameter and this query will generate the opening and closing stock as of current system date for all the warehouse
Declare @FromDate Datetime Declare @ToDate Datetime Declare @Whse nvarchar(10) select @FromDate = min(S0.Docdate) from dbo.OINM S0 where S0.Docdate >= getdate() select @ToDate = max(S1.Docdate) from dbo.OINM s1 where S1.Docdate <= getdate() 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 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 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 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
Hi,
In SQL Server, use the GETDATE() to get the current date.
ie Set @ToDate = GETDATE()
Regards
Edy
Dear Dect,
Try this
Declare @FromDate DateTime Declare @ToDate DateTime Declare @Group nvarchar(50) Declare @Whscode nvarchar(50) Set @FromDate = (Select min(S0.Docdate) from OIVL S0 where S0.Docdate >=GETDATE()) Set @ToDate = (Select max(S1.Docdate) from OIVL s1 where S1.Docdate <=GETDATE()) Set @Group = (Select Max(S2.ItmsGrpNam) from OITB S2 Where s2.ItmsGrpNam = '[Stockable Items]') Set @Whscode = (Select Max(S3.Loccode)from OIVL S3 Where s3.LocCode = 'DAMMAM') Select T0.ItemCode,T1.ItemName,T1.InvntryUom AS 'UNIT', SUM(CASE WHEN T0.DocDate<GETDATE() THEN (ISNULL(T0.InQty,0)-ISNULL(T0.OutQty,0)) ELSE 0 END) AS "OPENING QTY ", SUM(ISNULL(T0.InQty,0)-ISNULL(T0.OutQty,0)) AS "CLOSING QTY" From OIVL T0 Left JOIN OITM T1 ON T0.ItemCode=T1.ItemCode LEFT JOIN OITB T2 ON T1.ItmsGrpCod=T2.ItmsGrpCod Where --T0.[DOCDATE] >= '[%0]' AND T0.[DOCDATE] <= GETDATE() AND T2.ItmsGrpNam='Stockable Items' AND T0.Loccode='DAMMAM' Group By T0.ItemCode,T1.ItemName,T1.OnHand,T1.IsCommited,T1.OnOrder,T1.ItmsGrpCod,InvntryUom,T0.LocCode
Thanks
Engr. Taseeb Saeed
Add comment