One, repeat the expression for Pallet_Amt in your CASE expression where you choose between GRND and LTL. Yes, the expression will be long and look ugly.
Or, wrap your current query in a CTE or subquery and then select from the CTE like shown below
;WITH cte AS
(
--- your current query here
--- except for any order by clause you have at the end
)
SELECT
*,
CASE
WHEN Pallet_Amt < .75 THEN "GRND"
WHEN Pallet_Amt >= .75 THEN "LTL"
ELSE ""
END
FROM
cte
-- add any order by clause you may have.
I am having a doubt about the first part contained in the CTE. Will be grateful if you make it clear.
That is
;WITH cte AS
(
SELECT Pallet_Amt = sum(isnull(CASE
** WHEN ISNULL(od.Balance_Qty,0) > 0 and ISNULL(iplc.Per_Container_Qty,0) > 0 and ISNULL(iplc.Container_Volume,0) > 0 THEN ((od.Balance_Qty / iplc.Per_Container_Qty) * iplc.Container_Volume) / @cubicMeterPallet**
** ELSE 0**
END,0))
FROM Table
)
SELECT
*,
CASE
WHEN Pallet_Amt < .75 THEN "GRND"
WHEN Pallet_Amt >= .75 THEN "LTL"
ELSE ""
END
FROM
cte
I think my code needs some correction here. Thanks in advance.