Skip to Content

Query Item Opening and Closing qty

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
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

5 Answers

  • Jan 29, 2017 at 02:43 PM

    please help..

    Thanks

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 29, 2017 at 04:38 PM

    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

    Add comment
    10|10000 characters needed 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.

  • Jan 30, 2017 at 09:19 AM

    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

    Add comment
    10|10000 characters needed 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

  • Feb 01, 2017 at 01:12 AM

    Hi,

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

    ie Set @ToDate = GETDATE()

    Regards

    Edy

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Feb 01, 2017 at 06:27 AM

    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
    10|10000 characters needed characters exceeded