Skip to Content
0

Query Item Opening and Closing qty

Jan 29, 2017 at 09:02 AM

146

avatar image

Hello,

I have already create a code that will get the item opening and closing quantity on any given date. Please modify my code's below to get the current date so i will not going to enter the date's.

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 >='[%0]')
Set @ToDate = (Select max(S1.Docdate) from OIVL s1 where S1.Docdate <='[%1]')
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<@FromDate  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] <= '[%1]' 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
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

5 Answers

Dect Lariosa Jan 29, 2017 at 02:43 PM
0

please help..

Thanks

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Jan 29, 2017 at 04:38 PM
0

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

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Hi.

Thanks for the codes. Unable to execute the codes because of an error. And if possible eliminate the date parameter because i only want to query the opening and closing quantity within a day only. Reason is im going to put this in Alert so it will run automatically.

0
avatar image
Former Member Jan 30, 2017 at 09:19 AM
0

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

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Hi,

Thank you for your help, but the query return zero. No information came out. it says no data on the selection criteria. The item group i want to filter out is 'Stockable Items' under DAMMAM warehouse

0
Edy Simon Feb 01, 2017 at 01:12 AM
0

Hi,

In SQL Server, use the GETDATE() to get the current date.

ie Set @ToDate = GETDATE()

Regards

Edy

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Feb 01, 2017 at 06:27 AM
0

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

Share
10 |10000 characters needed characters left characters exceeded