[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 265
  • Last Modified:

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

0
gdspeare
Asked:
gdspeare
  • 2
1 Solution
 
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now