Inventory Balance at a Date

I’d like to share the following SQL query I put together with information from a couple posts on this site. Maybe someone will find this helpful to find the inventory part transaction balance of a part on a certain date. Each transaction has a multiplication factor which determines if the part is adjusted in, adjusted out, or not changed at all.

This post in particular was helpful: https://www.epiusers.help/t/parttran-tranqty-true-sign/35170/3

Here’s the query you can run in SSMS. Just fill in the four parameters and execute.

USE E10Live; 
GO

DECLARE @Company AS VARCHAR(2)  = '' -- fill this in
, @PartNum AS VARCHAR(50) = '' -- fill this in
, @Plant AS VARCHAR(10)   = '' -- fill this in
, @CutoffDate AS DATE     = '' -- fill this in (YYYY-MM-DD)
, @RunningTotal AS DECIMAL(12,2) = 0;

SELECT 
@CutoffDate AS 'CutoffDate'
, SUM(
CASE 
-- NEGATIVE ADJUSTMENTS
WHEN pt.TranType IN ('STK-ASM', 'STK-CUS', 'STK-INS', 'STK-KIT', 'STK-FAM', 
'STK-MTL', 'STK-PLT', 'STK-STK', 'STK-UKN', 'STK-DMR') 
THEN @RunningTotal + (-1 * pt.TranQty)

-- NO ADJUSTMENTS
WHEN pt.TranType IN ('ADJ-DRP', 'ADJ-MTL', 'ADJ-PUR', 'ADJ-SUB', 'ASM-INS', 'DMR-MTL',
'DMR-REJ', 'DMR-SUB', 'DRP-CUS', 'INS-ASM', 'INS-DMR', 'INS-MTL',
'INS-REJ', 'INS-SUB', 'KIT-CUS', 'MFG-CUS', 'MFG-PLT', 'MFG-VAR', 
'MFG-VEN', 'MFG-WIP', 'MTL-INS', 'MTL-DMR', 'PLT-MTL', 'PUR-CUS',
'PUR-DRP', 'PUR-INS', 'PUR-MTL', 'PUR-SUB', 'PUR-UKN', 'RMA-INS',
'SUB-INS', 'SUB-DMR', 'UKN-CUS', 'WIP-MFG') 
THEN 0 

-- POSITIVE ADJUSTMENTS
WHEN pt.TranType IN ('ADJ-CST', 'ADJ-QTY', 'AST-STK', 'DMR-STK', 'INS-STK', 
'MFG-STK', 'PLT-STK', 'PUR-STK', 'STK-STK', 'SVG-STK') 
THEN @RunningTotal + (1 * pt.TranQty)			
	
END ) AS 'BalanceAtCutoff'

FROM [dbo].PartTran AS pt WITH (NOLOCK)

WHERE pt.Company = @Company 
AND pt.PartNum = @PartNum 
AND pt.Plant   = @Plant
AND pt.TranDate <= @CutoffDate

(I know the 0 case has no effect but I just want to document the TRAN types that don’t affect inventory level inside the query)