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

Open in new window

gdspeareAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

folderolCommented:
The isnull and Coalesce functions are in essence in-line Case statements.  You only need to make this change

,((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')  +
isnull((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')),0) AS 'DNU TOTAL COMP'
 
0
folderolCommented:
Oops, I'm pretty sure I misplaced a parenthesis at the end, should be

,((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')  +
isnull((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'),0)) AS 'DNU TOTAL COMP'

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.