Case within Select

I need some help with a function in the below query.  The select statement immediately following the comment
---- Sum of ADJ (BUDGET + AUG TOTAL) budget by ID
has 2 pieces.  One side is calculating a number where the right(combo_code,3) is not like 'AUG' and the other is determing a value where the right(combo_code,3) is like 'AUG'.  The problem I am having is that I have records in my dataset where there may not be a right(combo_code,3) = 'AUG' instance and that is causing the formula to return a NULL value.

I need to add a case statement to do a count first and run one function or another depending on the returned value.

IN words the case statment should count if a uthscsa_id has a combo_code with the right(combo_code,3) ending in 'AUG' if so do ACTION 1 ELSE ACTION 2

select uthscsa_id
,prov_name
, b.fund_type
,uthscsa_budget
,sum(percent_effort)/100 AS 'Percent Effort'
---------    Calculate the sum FTE for each ID    ----------------
,(SELECT SUM(PERCENT_EFFORT)/100 FROM FTE_MASTER A1  WHERE A.UTHSCSA_ID = A1.UTHSCSA_ID) AS 'DNU PAID FTE CALC'
---------	 Sum of ADJ (BUDGET + AUG TOTAL) budget by ID                  ----------------
,((SELECT SUM(UTHSCSA_BUDGET * (percent_effort/100))/count(uthscsa_budget) FROM FTE_MASTER A2 WHERE A.UTHSCSA_ID = A2.UTHSCSA_ID AND RIGHT(combo_code,3) not like 'AUG')  +
	(Select	sum(uthscsa_budget)/count(uthscsa_budget) FROM FTE_MASTER A2 WHERE A.UTHSCSA_ID = A2.UTHSCSA_ID AND RIGHT(combo_code,3) like 'AUG')) AS 'DNU TOTAL COMP'
 
--------	 Determine if the combo code ends in "AUG."  If so, bring over the budget amount w/o an adjustment.  ----------
--------	 if the combo code does not end in "AUG", multiply the budget value for that record by the  original percent effort.  -------
, Case Right(Combo_Code,3)
	WHEN 'AUG' THEN uthscsa_budget
	ELSE uthscsa_budget * (percent_effort/100)
  END As COMP_ADJ
	--Determine the percent of revenue for each record by line.  The sum of this field is the revenue FTE ----------------
, Case Right(Combo_Code,3)
	When 'AUG' THEN --
		UTHSCSA_BUDGET/
		((SELECT SUM(UTHSCSA_BUDGET* percent_effort/100)/count(uthscsa_budget) FROM FTE_MASTER A2 WHERE A.UTHSCSA_ID = A2.UTHSCSA_ID AND RIGHT(combo_code,3) not like 'AUG')  +
	(Select	sum(uthscsa_budget)/count(uthscsa_budget) FROM FTE_MASTER A2 WHERE A.UTHSCSA_ID = A2.UTHSCSA_ID AND RIGHT(combo_code,3) like 'AUG'))*
	(SELECT SUM(PERCENT_EFFORT)/100 FROM FTE_MASTER A1  WHERE A.UTHSCSA_ID = A1.UTHSCSA_ID)	
	ELSE 
		CASE 
			WHEN (SELECT SUM(UTHSCSA_BUDGET) FROM FTE_MASTER A3 WHERE A.UTHSCSA_ID = A3.UTHSCSA_ID) = 0 THEN 0
		ELSE
			UTHSCSA_BUDGET * (PERCENT_EFFORT/100)/
 
			((SELECT SUM(UTHSCSA_BUDGET*percent_effort/100)/count(uthscsa_budget) FROM FTE_MASTER A2 WHERE A.UTHSCSA_ID = A2.UTHSCSA_ID AND RIGHT(combo_code,3) not like 'AUG')  +
			(Select	sum(uthscsa_budget)/count(uthscsa_budget) FROM FTE_MASTER A2 WHERE A.UTHSCSA_ID = A2.UTHSCSA_ID AND RIGHT(combo_code,3) like 'AUG')) *
			(SELECT SUM(PERCENT_EFFORT)/100 FROM FTE_MASTER A1  WHERE A.UTHSCSA_ID = A1.UTHSCSA_ID)
		END
 END AS REV_FTE_PERCENT
,A.dept_id AS 'DEPT ID'
,d.descr AS 'DEPT DESCR'
,A.funding_owner As 'FUND OWNER'
,C.descr AS 'FUND OWNER DESCR'
,C.utm_dept_id AS 'GP DEPT ID'
, b.fund_descr AS 'FUND DESC'
 
from fte_master a
	--Join is on a portion of the combo code field.
inner join fte_fund_detail b on (select substring(a.combo_code,8,5)) = b.fund_id
left outer join map_dept c on a.funding_owner = c.uthscsa_dept_id
inner join map_dept d on a.dept_id = d.uthscsa_dept_id
WHERE c.utm_dept_id like (select dept from users where rtrim(user_id) = suser_sName())	and uthscsa_id = '043105'
group by uthscsa_id,b.fund_type,prov_name,dept_id,c.descr,funding_owner,b.fund_descr,combo_code,uthscsa_budget,percent_effort,c.utm_dept_id,d.descr
order by prov_name

Select allOpen in new window