The following script shows the stock level of an item at a location at any particular date using non-HITB history tables.
You should be able to tweak the query to get the quantity variances between months.
DECLARE
@LOCATION varchar(11) = 'WAREHOUSE',
@ITEMNMBR varchar(31) = '128 SDRAM',
@Module VARCHAR(10),
@Type INT,
@DocNo VARCHAR(50),
@DocDate DATETIME,
@TrxQty FLOAT,
@OnHand FLOAT,
@Allocated FLOAT,
@From VARCHAR(50),
@To VARCHAR(50),
@Customer VARCHAR(50),
@CustomerName VARCHAR(100),
@Posted INT,
@DEX_ROW_ID INT
SELECT @Allocated = ATYALLOC, @OnHand = QTYONHND FROM IV00102 WHERE ITEMNMBR = @ITEMNMBR AND LOCNCODE = @LOCATION
CREATE TABLE #Temp
(
[Doc Date] DATETIME,
[Module] VARCHAR(10),
[Type] INT,
[Doc No] VARCHAR(50),
[Trx Qty] FLOAT,
[On Hand] FLOAT,
[Allocated] FLOAT,
[From] VARCHAR(50),
[To] VARCHAR(50),
[Customer] VARCHAR(50),
[Customer Name] VARCHAR(100),
[Posted] INT,
[DEX_ROW_ID] INT
)
DECLARE History_Cursor CURSOR FOR
-- Unposted invoices
SELECT
SOP10100.DOCDATE AS [DocDate],
'SOP' AS Module,
SOP10100.SOPTYPE AS [Type],
SOP10100.SOPNUMBE AS [DocNo],
SOP10200.QUANTITY AS [TrxQty],
SOP10200.LOCNCODE AS [From],
'' AS [To],
SOP10100.CUSTNMBR AS [Customer],
RM00101.CustName as [CustomerName],
0 as Posted,
SOP10100.DEX_ROW_ID
FROM SOP10200 AS SOP10200
INNER JOIN SOP10100 AS SOP10100 ON SOP10200.SOPNUMBE = SOP10100.SOPNUMBE
INNER JOIN RM00101 AS RM00101 ON SOP10100.CUSTNMBR = RM00101.CUSTNMBR
WHERE SOP10200.LOCNCODE = @LOCATION
AND SOP10200.ITEMNMBR = @ITEMNMBR
UNION
-- Unposted transfers and assemblies
SELECT
IV10000.DOCDATE AS [Doc Date],
'IV' AS Module,
IV10001.IVDOCTYP AS Type,
IV10001.IVDOCNBR AS [Doc No.],
IV10001.TRXQTY AS [TrxQty],
IV10001.TRXLOCTN AS [From],
IV10001.TRNSTLOC AS [To],
'' AS [Customer],
'' AS [Customer Name],
0 AS Posted,
IV10001.dex_row_id
FROM IV10001 AS IV10001
INNER JOIN IV10000 AS IV10000 ON IV10001.IVDOCNBR = IV10000.IVDOCNBR
WHERE IV10001.TRXLOCTN = @LOCATION AND IV10001.ITEMNMBR = @ITEMNMBR
UNION
-- Posted Invoices and non transfer
SELECT
IV30300.DOCDATE AS [DocDate],
IV30300.HSTMODUL AS Module,
IV30300.DOCTYPE AS [Type],
IV30300.DOCNUMBR AS [DocNo],
IV30300.TRXQTY AS [TrxQty],
IV30300.TRXLOCTN AS [From],
'' AS [To],
IV30300.CUSTNMBR AS [Customer],
CASE
WHEN RM00101.CUSTNMBR IS NOT NULL THEN RM00101.CUSTNAME
WHEN PM00200.VENDORID IS NOT NULL THEN PM00200.VENDNAME
ELSE '' END
AS [CustomerName],
1 AS Posted,
IV30300.DEX_ROW_ID
FROM IV30300 AS IV30300
LEFT JOIN RM00101 AS RM00101 ON IV30300.CUSTNMBR = RM00101.CUSTNMBR
LEFT JOIN PM00200 AS PM00200 ON IV30300.CUSTNMBR = PM00200.VENDORID
WHERE
IV30300.DOCTYPE <> 3
AND IV30300.TRXLOCTN = @LOCATION
AND IV30300.ITEMNMBR = @ITEMNMBR
UNION
-- Posted Transfers
SELECT
IV30300.DOCDATE AS [DocDate],
IV30300.HSTMODUL AS Module,
IV30300.DOCTYPE AS [Type],
IV30300.DOCNUMBR AS [DocNo],
-IV30300.TRXQTY AS [TrxQty],
'' AS [From],
IV30300.TRNSTLOC AS [To],
IV30300.CUSTNMBR AS [Customer],
'' AS [CustomerName],
1 AS Posted,
IV30300.DEX_ROW_ID
FROM
IV30300 AS IV30300
WHERE
IV30300.DOCTYPE = 3
AND IV30300.TRXLOCTN = @LOCATION
AND IV30300.ITEMNMBR = @ITEMNMBR
UNION
-- Posted transfers
SELECT
IV30300.DOCDATE AS [DocDate],
IV30300.HSTMODUL AS Module,
IV30300.DOCTYPE AS [Type],
IV30300.DOCNUMBR AS [DocNo],
IV30300.TRXQTY AS [TrxQty],
IV30300.TRXLOCTN AS [From],
'' AS [To],
IV30300.CUSTNMBR AS [Customer],
'' AS [CustomerName],
1 AS Posted,
IV30300.DEX_ROW_ID
FROM
IV30300 AS IV30300
WHERE
IV30300.DOCTYPE = 3
AND IV30300.ITEMNMBR = @ITEMNMBR
AND IV30300.TRNSTLOC = @LOCATION
ORDER BY Posted, DOCDATE DESC, [Quantity]
OPEN History_Cursor
FETCH NEXT FROM History_Cursor INTO @DocDate, @Module, @Type, @DocNo, @TrxQty, @From, @To, @Customer, @CustomerName, @Posted, @DEX_ROW_ID
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #Temp SELECT @DocDate, @Module, @Type, @DocNo, @TrxQty, @OnHand, @Allocated, @From, @To, @Customer, @CustomerName, @Posted, @DEX_ROW_ID
IF @Posted = 1
SET @OnHand = @OnHand - @TrxQty
IF @Posted = 0
BEGIN
IF CASE WHEN (@Module = 'IV' AND @Type IN (1,2,3)) THEN 0 ELSE 1 END = 1
AND CASE WHEN (@Module = 'SOP' AND @Type IN (4, 5)) THEN 0 ELSE 1 END = 1
SET @Allocated = @Allocated - @TrxQty
END
FETCH NEXT FROM History_Cursor INTO @DocDate, @Module, @Type, @DocNo, @TrxQty, @From, @To, @Customer, @CustomerName, @Posted, @DEX_ROW_ID
END
CLOSE History_Cursor
DEALLOCATE History_Cursor
SELECT * FROM #Temp
DROP TABLE #Temp
