Dynamics Community Forum Thread Details

Possible to determine lot variance sign?

For a query of the stock count history I can get the variance for individual lots, but coming from IV30400.SERLTQTY they are all positive. I can determine the overall variance of an item in a stock count using IV30701.VARIANCEQTY. Is it possible to determine individual lot variances.

SQL to get both lot (all positive) and overall variances:

*Item Numbers withheld.

SELECT DISTINCT CONVERT(VARCHAR(10), InventoryHistory.DOCDATE, 101) AS 'Date',
--StockHistory.ITEMNMBR AS 'Item',
LotHistory.SERLTNUM AS 'Lot Number',
LotHistory.SERLTQTY AS 'Lot Variance',
StockHistory.VARIANCEQTY AS 'Overall Variance',
StockHistory.DEX_ROW_ID

FROM IV30701 AS StockHistory
INNER JOIN IV30400 AS LotHistory
ON LotHistory.DOCNUMBR = StockHistory.IVDOCNBR
AND LotHistory.ITEMNMBR = StockHistory.ITEMNMBR 
INNER JOIN IV30200 AS InventoryHistory
ON InventoryHistory.DOCNUMBR = StockHistory.IVDOCNBR

WHERE StockHistory.VARIANCEQTY <> '0'

Resulting in this output:

Date Lot Number Lot Variance Overall Variance DEX_ROW_ID

01/31/2013 010313 104.52261 -1895.47739 5382
01/31/2013 010313 2000.00000 -1895.47739 5382

Note that for Lot Number 010313 the Lot Variances are seen as positive, but we can determine that the lot with a variance of 2000 is negative in order to arrive at their overall variance of -1895.4.

SQL demonstrating desired result, does not work as it is.

SELECT DISTINCT CONVERT(VARCHAR(10), InventoryHistory.DOCDATE, 101) AS 'Date',
--StockHistory.ITEMNMBR AS 'Item',
LotHistory.SERLTNUM AS 'Lot Number',
CASE
WHEN StockHistory.VARIANCEQTY < 0 
THEN -LotHistory.SERLTQTY
ELSE LotHistory.SERLTQTY
END AS 'Lot Variance',
StockHistory.DEX_ROW_ID

FROM IV30701 AS StockHistory
INNER JOIN IV30400 AS LotHistory
ON LotHistory.DOCNUMBR = StockHistory.IVDOCNBR
AND LotHistory.ITEMNMBR = StockHistory.ITEMNMBR 
INNER JOIN IV30200 AS InventoryHistory
ON InventoryHistory.DOCNUMBR = StockHistory.IVDOCNBR

WHERE StockHistory.VARIANCEQTY <> '0'

Resulting in this output:

Date Lot Number Lot Variance DEX_ROW_ID

01/31/2013 010313 -2000.00000 5382
01/31/2013 010313 -104.52261 5382

As you can see, this query does not work because it results in both lot variances being negative as the overall variance is negative. My question again is if it is possible to determine the individual lot variances?

Quick Links

Leaderboard