Stock Opening and closing report - Stock Summary report

2401 Views Last edit Mar 02, 2017 at 03:57 AM 2 rev

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.