How to create a case from a existing calculate column

Transact-SQL
1

I have a store proc that contains a column called "Pallet_Amt". I need to create another column that will display the type of Pallet_Amt.

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))

Example

New column base off of the Pallet_Amt

CASE
WHEN Pallet_Amt < .75 THEN "GRND"
WHEN Pallet_Amt >= .75 THEN "LTL"
ELSE ""
END

How can I do this without creating a temp file.

Thanks

2

Two possibilities:

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.
3

Thanks very very much!!!!

This worked great!

4

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.