Stock Opening and closing report - Stock Summary report

[Former Member]

Hello Experts,

I am working on a query, it works almost perfectly, the issue that I am facing is that, how can I get the Opening balance of the inventory that has been Input using "Inventory opening balance". I need to show the quantities that are added using Inventory opening balance as the opening stock.

requesting you to kindly help.

the query is as given below:

SELECT a.Groupname, SUM(a.[OB-Qty]) [OB-Qty], SUM(a.[Issue]) [Issue], SUM(a.[Receipt]) [Receipt], SUM(a.[Cls-Qty]) [Cls-Qty] FROM ( SELECT G1.ItmsGrpNam AS Groupname, (SUM(ISNULL(t1.inqty, 0)) - SUM(ISNULL(t1.outqty, 0))) [OB-Qty], SUM(ISNULL(t1.transvalue, 0)) [OB-Value], 0 [Issue], 0 [Receipt], 0 [Cls-Qty], 0 [ClsValue] FROM OINM t1 Inner join oitm m1 on m1.itemcode=t1.itemcode INNER JOIN oitb g1 ON g1.ItmsGrpCod=m1.ItmsGrpCod WHERE t1.docdate < '[%0]' AND t1.[Warehouse] = '[%3]' GROUP BY G1.ItmsGrpNam UNION ALL SELECT G1.ItmsGrpNam , 0 [OB-Qty], 0 [OB-Value], (SUM(ISNULL(t1.outqty, 0))) [Issue], (SUM(ISNULL(t1.inqty, 0))) [Receipt], 0 [Cls-Qty], 0 [ClsValue] FROM OINM t1 Inner join oitm m1 on m1.itemcode=t1.itemcode INNER JOIN oitb g1 ON g1.ItmsGrpCod=m1.ItmsGrpCod WHERE t1.docdate >= '[%0]' AND t1.DocDate <= '[%1]' AND t1.[Warehouse] = '[%3]' GROUP BY G1.ItmsGrpNam UNION ALL SELECT G1.ItmsGrpNam , 0 [OB-Qty], 0 [OB-Value], 0 [Issue], 0 [Receipt], (SUM(ISNULL(t1.inqty, 0)) - SUM(ISNULL(t1.outqty, 0))) [Cls-Qty], SUM(ISNULL(t1.transvalue, 0)) [ClsValue] FROM OINM t1 Inner join oitm m1 on m1.itemcode=t1.itemcode INNER JOIN oitb g1 ON g1.ItmsGrpCod=m1.ItmsGrpCod WHERE t1.DocDate <= '[%1]' AND t1.[Warehouse] = '[%3]' GROUP BY G1.ItmsGrpNam )a GROUP BY Groupname

thanks,

Ravi.

[Former Member]

Dear Vinoth,

the issue that i am facing here is, at the beginning of the year we have input stock using inventory opening balance, which is not getting reflected in the reports. I need to show that quantity as opening balance.

regards,

Ravi.

[former_member185296]

Hi,

Use this query to get the closing stock with the closing stock and item movement.

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

Regards,

Vinoth

[former_member679700]

Hi Vinoth,

I am using both your and Ravi queries as i found them very good.

However I need to take out the warehouse ..... i want to see the result for all the warehouse included and i find it hard to remove the @warehouse element .can you please help me and re-write the query without the warehouse selection?

many thanks

Francesco