Stone Coast Web Design's Weblog

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