Solved

# Case within Select

Posted on 2008-11-06
259 Views
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)

--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
``````
0
Question by:gdspeare

LVL 19

Expert Comment

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

LVL 19

Accepted Solution

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

### Suggested Solutions

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…

#### Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!