Friday, May 30, 2014

Table with adds and subtracts, trying to get the balance

Here is the full statement with your code added.



SELECT i.ItemID AS ID,
i.ItemName as Name,
(SELECT SupplyStatus
FROM SupplyStatus
WHERE SupplyStatusID = i.SupplyStatusID) AS CurrentStatus,
SUM (
CASE
WHEN VolumeOrNumber IS NULL THEN 0
WHEN AdjustmentTypeID = 1 THEN VolumeOrNumber
WHEN AdjustmentTypeID = 2 THEN VolumeOrNumber *-1
ELSE 0
END) AS InventoryTotal
FROM Item i
LEFT JOIN Inventory v ON i.ItemID = v.ItemID
LEFT JOIN DefaultContainerSizeUnit u ON i.DefaultContainerSizeUnitID = u.DefaultContainerSizeUnitID
WHERE i.CultivationFacilityID = 1
AND i.SupplyTypeID = 1
AND i.ItemTypeID = 1
AND (i.EndDate > GETDATE() OR i.EndDate IS NULL)
ORDER BY i.ItemName


No comments:

Post a Comment