The Balance
column shows current Stock on Hand + Open PO minus QTY Entered.
I have an existing query with ALIASes in the SELECT
statement:
SELECT dbo.Items.ItemNo,
dbo.Items.Name,
dbo.Vendors.Name AS VendorName,
ISNULL(ItemsStock.StockOnHand, 0) AS [Stock on Hand],
ISNULL(PO.[Open PO's'], 0) AS [Open PO],
ISNULL(WO.OpenWOQTY, 0) AS [QTY Entered].
How to add an additional column called Balance
to sum Stock on Hand and Open PO then deduct QTY Entered?
SELECT dbo.Items.ItemNo,
dbo.Items.Name,
dbo.Vendors.Name AS VendorName,
ISNULL(ItemsStock.StockOnHand, 0) AS [Stock on Hand],
ISNULL(PO.[Open PO's'], 0) AS [Open PO],
ISNULL(WO.OpenWOQTY, 0) AS [QTY Entered],
ISNULL(ItemsStock.StockOnHand, 0) + ISNULL(PO.[Open PO's'], 0) - ISNULL(WO.OpenWOQTY, 0) AS [Balance]
FROM table