June 5, 2014 at 9:43 am
DELETE had too many ENDs
June 5, 2014 at 10:08 am
without table structure, sample data, and expected results its hard to tell (see link in my signature)
I did notice that there appears to be so extra brackets in your case statements. does this work?
SELECTi.ItemID AS ID,
i.ItemName AS Name,
(SELECT SupplyStatus
FROM SupplyStatus
WHERE SupplyStatusID = i.SupplyStatusID) AS CurrentStatus,
CASE
WHEN i.SupplyTypeID <> 3 THEN
CAST(SUM (
CASE
WHEN VolumeOrNumber IS NULL THEN 0
WHEN AdjustmentTypeID = 1 THEN VolumeOrNumber
WHEN AdjustmentTypeID = 2 THEN VolumeOrNumber *-1
END) AS VARCHAR(10)) + ' ' + u.Abbreviation
ELSE
CAST(SUM (
CASE
WHEN VolumeOrNumber IS NULL THEN 0
WHEN AdjustmentTypeID = 1 THEN VolumeOrNumber
WHEN AdjustmentTypeID = 2 THEN VolumeOrNumber *-1
END) AS VARCHAR(10))
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.ItemTypeID = 1
AND (i.EndDate > GETDATE() OR i.EndDate IS NULL)
GROUP BY i.ItemID, i.ItemName, i.SupplyStatusID, u.Abbreviation
ORDER BY i.ItemName
June 5, 2014 at 10:08 am
June 5, 2014 at 10:18 am
You might have a lot of additional and unneeded code. 😉
However, I'm only guessing.
SELECTi.ItemID AS ID,
i.ItemName AS Name,
ss.SupplyStatus AS CurrentStatus,
CAST(SUM (
CASE AdjustmentTypeID
WHEN 1 THEN VolumeOrNumber
WHEN 2 THEN VolumeOrNumber *-1
END) AS VARCHAR(10))
+ CASE
WHEN i.SupplyTypeID <> 3
THEN ' ' + u.Abbreviation ELSE '' END AS InventoryTotal
FROM Item i
LEFT JOIN Inventory v ON i.ItemID = v.ItemID
LEFT JOIN DefaultContainerSizeUnit u ON i.DefaultContainerSizeUnitID = u.DefaultContainerSizeUnitID
JOIN SupplyStatus ss ON ss.SupplyStatusID = i.SupplyStatusID
WHERE i.CultivationFacilityID = 1
AND i.ItemTypeID = 1
AND (i.EndDate > GETDATE() OR i.EndDate IS NULL)
GROUP BY i.ItemID, i.ItemName, ss.SupplyStatus, u.Abbreviation
ORDER BY i.ItemName
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply