Skip to Content
0

Closing Inventory Report

Apr 13, 2017 at 10:59 AM

109

avatar image

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Luke Dunn Apr 14, 2017 at 01:45 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded
Hitul Varia May 11, 2017 at 06:48 AM
0

Hi Luke,

Thank you for your help.

Regards,

Hitul

Share
10 |10000 characters needed characters left characters exceeded