Referencing Case When statements in the same Select statement

Question

Hi, I am looking for some help with my SQL script. I would like to be able to reference some CASE WHEN functions within the same Select statement. So in the example below, I would like to create another CASE WHEN which is looking at two other CASE WHEN statements. E.g
CASE WHEN 'HARD DATA' = 'ACHIEVED' AND 'SOFT DATA' = 'ACHIEVED' THEN 'OVERALL ACHIEVED'
ELSE 'OVERALL FAILED' END AS 'OVERALL DATA'

---

My code is below:

---

SELECT

• FROM

(

SELECT

TF_FAM.INTEGRATED_FAMILY_ID AS 'FAMILY ID',

CASE

WHEN (EH_EPISODES1.[End Reason] = 'Outcomes Achieved' OR EH_EPISODES1.[End Reason] = 'Outcomes Achieved – support from Commissioned Service' OR EH_EPISODES1.[End Reason] = 'Outcomes Achieved – support from Open Access' OR EH_EPISODES1.[End Reason] = 'Outcome Achieved - plan in place and Work concluded - EHCP') THEN 'YES'

WHEN (EH_EPISODES1.[End Reason] = 'Advice and guidance only – support not required' OR EH_EPISODES1.[End Reason] = 'No unit involvement – Open Access' OR EH_EPISODES1.[End Reason] = 'Advice and guidance only - EHCP') AND (EH_EPISODES2.[End Reason] = 'Outcomes Achieved' OR EH_EPISODES2.[End Reason] = 'Outcomes Achieved – support from Commissioned Service' OR EH_EPISODES2.[End Reason] = 'Outcomes Achieved – support from Open Access' OR EH_EPISODES2.[End Reason] = 'Outcome Achieved - plan in place and Work concluded - EHCP') THEN 'YES'

ELSE 'NO' END AS 'CLOSURE STATUS',

MEMBER_COUNT_2 AS 'MEMBER COUNT',

HEADLINES_MET,

CASE WHEN TF_IND.OUTCOME_REFERENCE IN ('1.1','1.2','6.2_Data','7.1','7.3','8.1_Data','8.2_Data','8.3_Data','10.3') and (FAM_IND.DIM_LOOKUP_TF_PROGRESS_STATUS_DESC = 'NULL' OR FAM_IND.DIM_LOOKUP_TF_PROGRESS_STATUS_DESC = 'ACHIEVED') THEN 'Achieved'

WHEN TF_IND.OUTCOME_REFERENCE IN ('1.1','1.2','6.2_Data','7.1','7.3','8.1_Data','8.2_Data','8.3_Data','10.3') and (FAM_IND.DIM_LOOKUP_TF_PROGRESS_STATUS_DESC <> 'NULL' OR FAM_IND.DIM_LOOKUP_TF_PROGRESS_STATUS_DESC <> 'ACHIEVED') THEN 'not Achieved'

ELSE 'n/a' END AS 'HARD DATA',

CASE WHEN TF_IND.OUTCOME_REFERENCE IN ('1.3','1.4','2.1','2.2','2.3','3.1','3.2','3.3','4.1','4.2','5.1','5.2','5.3','5.4','6.1','6.2','6.3','6.4','6.5','6.6','7.2','8.1','8.2','8.3','9.1','9.2','9.3','10.1','10.2') and (FAM_IND.DIM_LOOKUP_TF_PROGRESS_STATUS_DESC = 'NULL' OR FAM_IND.DIM_LOOKUP_TF_PROGRESS_STATUS_DESC = 'ACHIEVED') THEN 'Achieved'

WHEN TF_IND.OUTCOME_REFERENCE IN ('1.3','1.4','2.1','2.2','2.3','3.1','3.2','3.3','4.1','4.2','5.1','5.2','5.3','5.4','6.1','6.2','6.3','6.4','6.5','6.6','7.2','8.1','8.2','8.3','9.1','9.2','9.3','10.1','10.2') and (FAM_IND.DIM_LOOKUP_TF_PROGRESS_STATUS_DESC <> 'NULL' OR FAM_IND.DIM_LOOKUP_TF_PROGRESS_STATUS_DESC <> 'ACHIEVED') THEN 'not Achieved'

ELSE 'n/a' END AS 'SOFT DATA',

REGRESSION.[Family Regression],

--These are the two objects used in the pivot table at end

FAM_IND.DIM_LOOKUP_TF_PROGRESS_STATUS_DESC,

TF_IND.OUTCOME_REFERENCE

---

FROM [kcc-dba722].HDM.Singleview.DIM_TF_FAMILY TF_FAM with(nolock)

Answer 1

Case is useful for simple if-else checks but when you start getting to the complexity you're looking for here they start to break down. I can't directly address the perf issues but I suspect it'll impact the query performance the more "nested" you get.

At this point 2 solutions come to mind. The first solution is to use a view to do this calculation instead of directly in your query. Have the view return back the computed columns from the cases and then your higher level query can just treat them as normal columns, including using them in other case statements. Alternatively if the data is static and something you need a lot of perhaps a computed column on the corresponding table would be better. It really depends if this data is based upon a single row's information (computed column) or multiple rows/tables (view). In either case move this out of the query itself.

If the information is only needed for this one query then an alternative to a full view is using a common table expression. Move the query building up the calculated values into one or more CTEs. Then select from the CTE like you would any other value. It would be difficult to show you an example against your existing query as we don't have your table information. If you can provide a minimally reproducible table and dataset to demo what you're trying to do then we can probably put together a CTE to do it.

Answer 2

Hi Michael,
Thank you for your very quick and detailed response! I have used CTE tables previously but I wondered if there was an alternative as I found myself having to create lots of CTE tables where I was referencing each one. So I had a CTE table with CASE WHEN statements in it, I then called on that CTE table which included a new CASE WHEN, and then needed to reference that new CASE WHEN so needed to create yet another CTE table so it all got very long!

I assumed there should be an easier way to reference CASE WHEN's to create new CASE WHEN's....Its kind of the equivalent of referencing other formulas in EXCEL. I just cant get my head around it and figure I need to learn the best way as this is something I am likely to need to do many times.

Thanks, Lee

Answer 3

Since you are interested in an alternative, then check this approach:

select ...,
  t.HARD_DATA as [HARD DATA],
  CASE WHEN t.[HARD DATA] = 'ACHIEVED' AND t.[SOFT DATA] = 'ACHIEVED' THEN 'OVERALL ACHIEVED'
  ELSE 'OVERALL FAILED' END AS [OVERALL DATA]
FROM ...
cross apply (values (
   CASE WHEN TF_IND.OUTCOME_REFERENCE IN ('1.1','1.2','6.2_Data','7.1','7.3','8.1_Data','8.2_Data','8.3_Data','10.3') and (FAM_IND.DIM_LOOKUP_TF_PROGRESS_STATUS_DESC = 'NULL' OR FAM_IND.DIM_LOOKUP_TF_PROGRESS_STATUS_DESC = 'ACHIEVED') THEN 'Achieved'
WHEN TF_IND.OUTCOME_REFERENCE IN ('1.1','1.2','6.2_Data','7.1','7.3','8.1_Data','8.2_Data','8.3_Data','10.3') and (FAM_IND.DIM_LOOKUP_TF_PROGRESS_STATUS_DESC <> 'NULL' OR FAM_IND.DIM_LOOKUP_TF_PROGRESS_STATUS_DESC <> 'ACHIEVED') THEN 'not Achieved'
ELSE 'n/a' END,
   CASE WHEN TF_IND.OUTCOME_REFERENCE IN ('1.3','1.4','2.1','2.2','2.3','3.1','3.2','3.3','4.1','4.2','5.1','5.2','5.3','5.4','6.1','6.2','6.3','6.4','6.5','6.6','7.2','8.1','8.2','8.3','9.1','9.2','9.3','10.1','10.2') and (FAM_IND.DIM_LOOKUP_TF_PROGRESS_STATUS_DESC = 'NULL' OR FAM_IND.DIM_LOOKUP_TF_PROGRESS_STATUS_DESC = 'ACHIEVED') THEN 'Achieved'
WHEN TF_IND.OUTCOME_REFERENCE IN ('1.3','1.4','2.1','2.2','2.3','3.1','3.2','3.3','4.1','4.2','5.1','5.2','5.3','5.4','6.1','6.2','6.3','6.4','6.5','6.6','7.2','8.1','8.2','8.3','9.1','9.2','9.3','10.1','10.2') and (FAM_IND.DIM_LOOKUP_TF_PROGRESS_STATUS_DESC <> 'NULL' OR FAM_IND.DIM_LOOKUP_TF_PROGRESS_STATUS_DESC <> 'ACHIEVED') THEN 'not Achieved'
ELSE 'n/a' END)) t([HARD DATA], [SOFT DATA])