I was recently working on a project where I had to return the larger of two values in my T-SQL code. COALESCE would not work because often one of the two values was not NULL which COALESCE requires. The solution ended up being relatively simple by using a CASE-WHEN statement like below.
CASE
WHEN CEOAnnualBonus > CEOBonus THEN CEOAnnualBonus
WHEN CEOBonus IS NULL THEN CEOAnnualBonus
ELSE CEOBonus
END
AS CEOBonusCombined,
CASE
WHEN CEOOtherAnnualComp > CEOAllOtherCompensation THEN CEOOtherAnnualComp
WHEN CEOAllOtherCompensation IS NULL THEN CEOOtherAnnualComp
ELSE
CEOAllOtherCompensation
END
AS CEOAnnualCompCombined
Also, I learned about using ISNULL to return a different value when you encounter a NULL value in your data. This helps clean it up at run time.
ISNULL(dbo.TBenchmarkTemp.ChairIndependent, '-') AS ChairIndependent