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