Closing Inventory Report

[former_member269992]

Hello All,

I am using Following Query to get the Inventory Stock from particular warehouse.

DECLARE @PARAM1 DATETIME;
SET @PARAM1 = (SELECT T0.[DocDate] FROM OINM T0 WHERE T0.[DocDate] = [%0] GROUP BY T0.[DocDate]);

SELECT TOP 100 PERCENT A.[ItemCode], A.[ItemName], A.[ItemGroup], str(A.Qty,19,6) AS QTY, A.Value, A.[Warehouse]

FROM

(SELECT
T0.[ItemCode], T1.[ItemName], T2.[ItmsGrpNam] AS [ItemGroup], T0.[Warehouse], SUM(T0.[InQty] - T0.[OutQty]) AS Qty, SUM(TransValue) AS Value
FROM
OINM T0 INNER JOIN
OITM T1 ON T0.ItemCode = T1.ItemCode INNER JOIN
OITB T2 ON T1.ItmsGrpCod = T2.ItmsGrpCod
WHERE
T0.[Warehouse] = [%1] AND T0.[DocDate] <= @PARAM1
GROUP BY
T0.[ItemCode], T1.[ItemName], T2.[ItmsGrpNam], T0.[Warehouse]) AS A

WHERE
(A.Qty <> 0 OR A.Value <> 0)

ORDER BY A.[ItemCode]

But, It is not working for some dates. giving NO DATA Found

For eg: it is giving correct result for 30/03/2017, but giving NO DATA FOUND for 31/03/17.

Please advise, what's changes I should make to resolve this issue.

Thanks & regards,

Hitul

[Former Member · Accepted solution]

Hi Hitul

The issue is caused by your Declare at the top of the query. please try the below instead. This worked for me.

SELECT TOP 100 PERCENT A.[ItemCode], A.[ItemName], A.[ItemGroup], str(A.Qty,19,6) AS QTY, A.Value, A.[Warehouse]

FROM

(SELECT
T0.[ItemCode], T1.[ItemName], T2.[ItmsGrpNam] AS [ItemGroup], T0.[Warehouse], SUM(T0.[InQty] - T0.[OutQty]) AS Qty, SUM(TransValue) AS Value
FROM
OINM T0 INNER JOIN
OITM T1 ON T0.ItemCode = T1.ItemCode INNER JOIN
OITB T2 ON T1.ItmsGrpCod = T2.ItmsGrpCod
WHERE
T0.[Warehouse] = [%1] AND T0.[DocDate] <= [%0]
GROUP BY
T0.[ItemCode], T1.[ItemName], T2.[ItmsGrpNam], T0.[Warehouse]) AS A

WHERE
(A.Qty <> 0 OR A.Value <> 0)

I hope this helps

Kind regards

-Luke

[former_member269992]

Hi Luke,

Thank you for your help.

Regards,

Hitul