Joining to a lookup table using calculated field based on CASE statement

Question

Post reply

Joining to a lookup table using calculated field based on CASE statement

Canazei65

SSCommitted

Points: 1696
More actions
April 7, 2011 at 9:00 am

#235299

I have created a column called 'PrimarySecondary_ICD' using CASE to populate it from other columns. The column contains 3 character alphanumeric codes. These 3 char alphanumeric codes exist in a lookup table ('RK_tblInjCodes ') containing the code descriptions.
Basically, I need to join the column 'PrimarySecondary_ICD' in the SELECT to the column 'Codes' in the lookup table to retrieve the descriptions .
Can anyone advise an efficient way to do this?
Any suggestions on speeding up the query would also be gratefully received.
I have also tried selecting into a temp table and using 'UPDATE' to populate additions columns with code descriptions, but the retrieval time is excessive and I'm trying to find another method (I've tried creating an index on the temp table too, but I receive the error message 'Cannot find the object "##MyTempTbl" because it does not exist or you do not have permissions', so I guess that's out for now too).
the code is as follows:
SELECT ROW_NUMBER() OVER (
ORDER BY Process_ID) AS RowNumber,
Process_ID,
case
when AIMTC_ProviderSpell_End_Date between '01-APR-2003' and '31-MAR-2004' then '2003/04'
when AIMTC_ProviderSpell_End_Date between '01-APR-2004' and '31-MAR-2005' then '2004/05'
when AIMTC_ProviderSpell_End_Date between '01-APR-2005' and '31-MAR-2006' then '2005/06'
when AIMTC_ProviderSpell_End_Date between '01-APR-2006' and '31-MAR-2007' then '2006/07'
when AIMTC_ProviderSpell_End_Date between '01-APR-2007' and '31-MAR-2008' then '2007/08'
when AIMTC_ProviderSpell_End_Date between '01-APR-2008' and '31-MAR-2009' then '2008/09'
when AIMTC_ProviderSpell_End_Date between '01-APR-2009' and '31-MAR-2010' then '2009/10'
when AIMTC_ProviderSpell_End_Date between '01-APR-2010' and '31-MAR-2011' then '2010/11'
when AIMTC_ProviderSpell_End_Date between '01-APR-2011' and '31-MAR-2012' then '2011/12'
when AIMTC_ProviderSpell_End_Date between '01-APR-2012' and '31-MAR-2013' then '2012/13'
when AIMTC_ProviderSpell_End_Date between '01-APR-2013' and '31-MAR-2014' then '2013/14'
when AIMTC_ProviderSpell_End_Date between '01-APR-2014' and '31-MAR-2015' then '2014/15'
when AIMTC_ProviderSpell_End_Date between '01-APR-2015' and '31-MAR-2016' then '2015/16'
end as 'Fin_year',
AIMTC_ProviderSpell_End_Date,
convert(varchar(10),AIMTC_ProviderSpell_End_Date, 103) as AIMTC_ProviderSpell_End_Date_British,
DiagnosisPrimary_ICD,
Diagnosis1stSecondary_ICD,
Diagnosis2ndSecondary_ICD,
Diagnosis3rdSecondary_ICD,
Diagnosis4thSecondary_ICD,
Diagnosis5thSecondary_ICD,
Diagnosis6thSecondary_ICD,
Diagnosis7thSecondary_ICD,
Diagnosis8thSecondary_ICD,
Diagnosis9thSecondary_ICD,
Diagnosis10thSecondary_ICD,
Diagnosis11thSecondary_ICD,
Diagnosis12thSecondary_ICD,
Diagnosis13thSecondary_ICD,
Diagnosis14thSecondary_ICD,
PrimarySecondary_ICD = CASE
WHEN Diagnosis1stSecondary_ICD >= 'V000' AND Diagnosis1stSecondary_ICD < 'Y99 ' THEN LEFT(Diagnosis1stSecondary_ICD,3)
WHEN Diagnosis2ndSecondary_ICD >= 'V000' AND Diagnosis2ndSecondary_ICD < 'Y99 ' THEN LEFT(Diagnosis2ndSecondary_ICD,3)
WHEN Diagnosis3rdSecondary_ICD >= 'V000' AND Diagnosis3rdSecondary_ICD < 'Y99 ' THEN LEFT(Diagnosis3rdSecondary_ICD,3)
WHEN Diagnosis4thSecondary_ICD >= 'V000' AND Diagnosis4thSecondary_ICD < 'Y99 ' THEN LEFT(Diagnosis4thSecondary_ICD,3)
WHEN Diagnosis5thSecondary_ICD >= 'V000' AND Diagnosis5thSecondary_ICD < 'Y99 ' THEN LEFT(Diagnosis5thSecondary_ICD,3)
WHEN Diagnosis6thSecondary_ICD >= 'V000' AND Diagnosis6thSecondary_ICD < 'Y99 ' THEN LEFT(Diagnosis6thSecondary_ICD,3)
WHEN Diagnosis7thSecondary_ICD >= 'V000' AND Diagnosis7thSecondary_ICD < 'Y99 ' THEN LEFT(Diagnosis7thSecondary_ICD,3)
WHEN Diagnosis8thSecondary_ICD >= 'V000' AND Diagnosis8thSecondary_ICD < 'Y99 ' THEN LEFT(Diagnosis8thSecondary_ICD,3)
WHEN Diagnosis9thSecondary_ICD >= 'V000' AND Diagnosis9thSecondary_ICD < 'Y99 ' THEN LEFT(Diagnosis9thSecondary_ICD,3)
WHEN Diagnosis10thSecondary_ICD >= 'V000' AND Diagnosis10thSecondary_ICD < 'Y99 ' THEN LEFT(Diagnosis10thSecondary_ICD,3)
WHEN Diagnosis11thSecondary_ICD >= 'V000' AND Diagnosis11thSecondary_ICD < 'Y99 ' THEN LEFT(Diagnosis11thSecondary_ICD,3)
WHEN Diagnosis12thSecondary_ICD >= 'V000' AND Diagnosis12thSecondary_ICD < 'Y99 ' THEN LEFT(Diagnosis12thSecondary_ICD,3)
WHEN Diagnosis13thSecondary_ICD >= 'V000' AND Diagnosis13thSecondary_ICD < 'Y99 ' THEN LEFT(Diagnosis13thSecondary_ICD,3)
WHEN Diagnosis14thSecondary_ICD >= 'V000' AND Diagnosis14thSecondary_ICD < 'Y99 ' THEN LEFT(Diagnosis14thSecondary_ICD,3)
END,
r.SecondaryGroupDesc,---this relies on the lookup table in the join below
r.PrimaryGroupDesc,---this relies on the lookup table in the join below
--SPACE(250) AS PrimarySecondary_ICD_Description,
--SPACE(250) AS PrimaryGroupDesc,
AIMTC_Age,
Year(AIMTC_ProviderSpell_End_Date) as 'Year',
Month(AIMTC_ProviderSpell_End_Date) as 'Month',
--SPACE(3) AS MthCHAR1,
datename(month,AIMTC_ProviderSpell_End_Date) as MthCHAR1,
CONVERT(varchar(3),AIMTC_ProviderSpell_End_Date, 100) as CHAR2,
AIMTC_PCTResidence
--into #Inj
FROM zag.abi.dbo.vw_APC_SEM_001,
zag.Analyst_SQL_Area.dbo.RK_tblInjCodes r -- code descriptions are in here
where PrimarySecondary_ICD = r.Codes -- this join won't work
order by Process_ID
Cheers

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

why can't you just wrap the big query as a subselect or CTE, and then join to your lookup table:

you know,

Select

MyAlias.*,

RK_tblInjCodes.*

FROM (

--big query goes here

--left out for clarity of concept

--inner query cannot have ORDER BY

)MyAlias

LEFT OUTER JOIN RK_tblInjCodes

ON MyAlias.PrimarySecondary_ICD = RK_tblInjCodes.Codes

order by MyAlias.Process_ID

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

Another suggestion is to use your end date to calculate the Fin_Year instead of bring forced to change this every year

select

case

when MONTH(AIMTC_ProviderSpell_End_Date) > 4

then CAST(year(AIMTC_ProviderSpell_End_Date) - 1 as varchar(4)) + '/' + CAST(year(AIMTC_ProviderSpell_End_Date) as varchar(4))

else

case

when AIMTC_ProviderSpell_End_Date <= '3/31/' + CAST(year(AIMTC_ProviderSpell_End_Date) as varchar(4))

then CAST(year(AIMTC_ProviderSpell_End_Date) - 1 as varchar(4)) + '/' + CAST(year(AIMTC_ProviderSpell_End_Date) as varchar(4))

else

CAST(year(AIMTC_ProviderSpell_End_Date) as varchar(4)) + '/' + CAST(year(AIMTC_ProviderSpell_End_Date) + 1 as varchar(4))

end

end as Fin_year

Here is some debug code to confirm that this works.

declare @EndDate datetime = '4/21/2025'

select

case

when MONTH(@EndDate) > 4

then CAST(year(@EndDate) - 1 as varchar(4)) + '/' + CAST(year(@EndDate) as varchar(4))

else

case

when @EndDate <= '3/31/' + CAST(year(@EndDate) as varchar(4))

then CAST(year(@EndDate) - 1 as varchar(4)) + '/' + CAST(year(@EndDate) as varchar(4))

else

CAST(year(@EndDate) as varchar(4)) + '/' + CAST(year(@EndDate) + 1 as varchar(4))

end

end as Fin_year

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

You could also get the big query to do what you want by using the big case statement instead of the derived column name.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Canazei65 SSCommitted Points: 1696 More actions · Answer 4

Sorry about the late reply, I've been away from my desk most of the time since my in initial post.

Anyway, thanks guys, I'm very grateful for your assistance which I have used to refine my statement, although Sean I'm not too clear on what your last post actually means (sorry, if I'm being a bit stupid).

My latest query (in full) this time, now looks like:

Select

--MyAlias.*,--use this to select all the fields from the inner CTE SELECT below

--RK_tblInjCodes.*--use this to select all the fields from the lookup table with the Injury Code Descriptions

MyAlias.RowNumber,

MyAlias.PCT,

RK_tblInjCodes.Code,

RK_tblInjCodes.SecondaryGroupDesc,

RK_tblInjCodes.PrimaryGroupDesc,

MyAlias.ProvSpellEnd,

MyAlias.ProvSpellEndBritish,

MyAlias.Fin_Year,

MyAlias.Year,

MyAlias.Month,

--MyAlias.MthCHAR1,

MyAlias.MthCHAR2,

MyAlias.Number

FROM

--CTE (Big Query) starts next i.e. You can select (as above) from this SELECT as if it's a temp table

( SELECT ROW_NUMBER() OVER (

ORDER BY Process_ID) AS RowNumber,

Process_ID,

case

when MONTH(AIMTC_ProviderSpell_End_Date) > 12

then CAST(year(AIMTC_ProviderSpell_End_Date) - 1 as varchar(4)) + '/' + CAST(year(AIMTC_ProviderSpell_End_Date) as varchar(4))

else

case

when AIMTC_ProviderSpell_End_Date <= '3/31/' + CAST(year(AIMTC_ProviderSpell_End_Date) as varchar(4))

then CAST(year(AIMTC_ProviderSpell_End_Date) - 1 as varchar(4)) + '/' + CAST(year(AIMTC_ProviderSpell_End_Date) as varchar(4))

else

CAST(year(AIMTC_ProviderSpell_End_Date) as varchar(4)) + '/' + CAST(year(AIMTC_ProviderSpell_End_Date) + 1 as varchar(4))

end

end as Fin_Year,

AIMTC_ProviderSpell_End_Date as ProvSpellEnd,

convert(varchar(10),AIMTC_ProviderSpell_End_Date, 103) as ProvSpellEndBritish,

/*DiagnosisPrimary_ICD,

Diagnosis1stSecondary_ICD,

Diagnosis2ndSecondary_ICD,

Diagnosis3rdSecondary_ICD,

Diagnosis4thSecondary_ICD,

Diagnosis5thSecondary_ICD,

Diagnosis6thSecondary_ICD,

Diagnosis7thSecondary_ICD,

Diagnosis8thSecondary_ICD,

Diagnosis9thSecondary_ICD,

Diagnosis10thSecondary_ICD,

Diagnosis11thSecondary_ICD,

Diagnosis12thSecondary_ICD,

Diagnosis13thSecondary_ICD,

Diagnosis14thSecondary_ICD,*/

PrimarySecondary_ICD = CASE

WHEN Diagnosis1stSecondary_ICD >= 'V000' AND Diagnosis1stSecondary_ICD < 'Y99 ' THEN LEFT(Diagnosis1stSecondary_ICD,3)

WHEN Diagnosis2ndSecondary_ICD >= 'V000' AND Diagnosis2ndSecondary_ICD < 'Y99 ' THEN LEFT(Diagnosis2ndSecondary_ICD,3)

WHEN Diagnosis3rdSecondary_ICD >= 'V000' AND Diagnosis3rdSecondary_ICD < 'Y99 ' THEN LEFT(Diagnosis3rdSecondary_ICD,3)

WHEN Diagnosis4thSecondary_ICD >= 'V000' AND Diagnosis4thSecondary_ICD < 'Y99 ' THEN LEFT(Diagnosis4thSecondary_ICD,3)

WHEN Diagnosis5thSecondary_ICD >= 'V000' AND Diagnosis5thSecondary_ICD < 'Y99 ' THEN LEFT(Diagnosis5thSecondary_ICD,3)

WHEN Diagnosis6thSecondary_ICD >= 'V000' AND Diagnosis6thSecondary_ICD < 'Y99 ' THEN LEFT(Diagnosis6thSecondary_ICD,3)

WHEN Diagnosis7thSecondary_ICD >= 'V000' AND Diagnosis7thSecondary_ICD < 'Y99 ' THEN LEFT(Diagnosis7thSecondary_ICD,3)

WHEN Diagnosis8thSecondary_ICD >= 'V000' AND Diagnosis8thSecondary_ICD < 'Y99 ' THEN LEFT(Diagnosis8thSecondary_ICD,3)

WHEN Diagnosis9thSecondary_ICD >= 'V000' AND Diagnosis9thSecondary_ICD < 'Y99 ' THEN LEFT(Diagnosis9thSecondary_ICD,3)

WHEN Diagnosis10thSecondary_ICD >= 'V000' AND Diagnosis10thSecondary_ICD < 'Y99 ' THEN LEFT(Diagnosis10thSecondary_ICD,3)

WHEN Diagnosis11thSecondary_ICD >= 'V000' AND Diagnosis11thSecondary_ICD < 'Y99 ' THEN LEFT(Diagnosis11thSecondary_ICD,3)

WHEN Diagnosis12thSecondary_ICD >= 'V000' AND Diagnosis12thSecondary_ICD < 'Y99 ' THEN LEFT(Diagnosis12thSecondary_ICD,3)

WHEN Diagnosis13thSecondary_ICD >= 'V000' AND Diagnosis13thSecondary_ICD < 'Y99 ' THEN LEFT(Diagnosis13thSecondary_ICD,3)

WHEN Diagnosis14thSecondary_ICD >= 'V000' AND Diagnosis14thSecondary_ICD < 'Y99 ' THEN LEFT(Diagnosis14thSecondary_ICD,3)

END,

AIMTC_Age,

Year(AIMTC_ProviderSpell_End_Date) as 'Year',

Month(AIMTC_ProviderSpell_End_Date) as 'Month',

--datename(month,AIMTC_ProviderSpell_End_Date) as MthCHAR1,

CONVERT(varchar(3),AIMTC_ProviderSpell_End_Date, 100) as MthCHAR2,

AIMTC_PCTResidence as PCT,

Count (*) as number

FROM zag.abi.dbo.vw_APC_SEM_001

WHERE

AIMTC_AdmissionMethod_HospitalProviderSpell in (21,22,23,24,28)--emergency admissions

AND

AIMTC_ProviderSpell_End_Date >= '01-APR-2003'

AND

AIMTC_PCTResidence in ('5QJ','5FL','5M8','5A3')

AND

AIMTC_Age < '18'

AND

AIMTC_SEQ = 1--only 1st episodes

AND

((Diagnosis1stSecondary_ICD between 'V000' and 'Y98%' OR

Diagnosis2ndSecondary_ICD between 'V000' and 'Y98%' OR

Diagnosis3rdSecondary_ICD between 'V000' and 'Y98%' OR

Diagnosis4thSecondary_ICD between 'V000' and 'Y98%' OR

Diagnosis5thSecondary_ICD between 'V000' and 'Y98%' OR

Diagnosis6thSecondary_ICD between 'V000' and 'Y98%' OR

Diagnosis7thSecondary_ICD between 'V000' and 'Y98%' OR

Diagnosis8thSecondary_ICD between 'V000' and 'Y98%' OR

Diagnosis9thSecondary_ICD between 'V000' and 'Y98%' OR

Diagnosis10thSecondary_ICD between 'V000' and 'Y98%' OR

Diagnosis11thSecondary_ICD between 'V000' and 'Y98%' OR

Diagnosis12thSecondary_ICD between 'V000' and 'Y98%' OR

Diagnosis13thSecondary_ICD between 'V000' and 'Y98%' OR

Diagnosis14thSecondary_ICD between 'V000' and 'Y98%')

And --exclusions

(Diagnosis1stSecondary_ICD not between 'X330' and 'X399' OR

Diagnosis2ndSecondary_ICD not between 'X330' and 'X399' OR

Diagnosis3rdSecondary_ICD not between 'X330' and 'X399' OR

Diagnosis4thSecondary_ICD not between 'X330' and 'X399' OR

Diagnosis5thSecondary_ICD not between 'X330' and 'X399' OR

Diagnosis6thSecondary_ICD not between 'X330' and 'X399' OR

Diagnosis7thSecondary_ICD not between 'X330' and 'X399' OR

Diagnosis8thSecondary_ICD not between 'X330' and 'X399' OR

Diagnosis9thSecondary_ICD not between 'X330' and 'X399' OR

Diagnosis10thSecondary_ICD not between 'X330' and 'X399' OR

Diagnosis11thSecondary_ICD not between 'X330' and 'X399' OR

Diagnosis12thSecondary_ICD not between 'X330' and 'X399' OR

Diagnosis13thSecondary_ICD not between 'X330' and 'X399' OR

Diagnosis14thSecondary_ICD not between 'X330' and 'X399')

And

(Diagnosis1stSecondary_ICD <> 'X52%'OR

Diagnosis2ndSecondary_ICD <> 'X52%' OR

Diagnosis3rdSecondary_ICD <> 'X52%' OR

Diagnosis4thSecondary_ICD <> 'X52%' OR

Diagnosis5thSecondary_ICD <> 'X52%' OR

Diagnosis6thSecondary_ICD <> 'X52%' OR

Diagnosis7thSecondary_ICD <> 'X52%' OR

Diagnosis8thSecondary_ICD <> 'X52%' OR

Diagnosis9thSecondary_ICD <> 'X52%' OR

Diagnosis10thSecondary_ICD <> 'X52%' OR

Diagnosis11thSecondary_ICD <> 'X52%' OR

Diagnosis12thSecondary_ICD <> 'X52%' OR

Diagnosis13thSecondary_ICD <> 'X52%' OR

Diagnosis14thSecondary_ICD <> 'X52%'))

group by

Process_ID,

DiagnosisPrimary_ICD,Diagnosis1stSecondary_ICD,Diagnosis2ndSecondary_ICD,Diagnosis3rdSecondary_ICD,

Diagnosis4thSecondary_ICD,Diagnosis5thSecondary_ICD,Diagnosis6thSecondary_ICD,Diagnosis7thSecondary_ICD,

Diagnosis8thSecondary_ICD,Diagnosis9thSecondary_ICD,Diagnosis10thSecondary_ICD,Diagnosis11thSecondary_ICD,

Diagnosis12thSecondary_ICD,Diagnosis13thSecondary_ICD,Diagnosis14thSecondary_ICD, AIMTC_Age ,

Year(AIMTC_ProviderSpell_End_Date),Month(AIMTC_ProviderSpell_End_Date), AIMTC_ProviderSpell_End_Date,

AIMTC_PCTResidence

--inner query cannot have ORDER BY

)MyAlias

LEFT OUTER JOIN RK_tblInjCodes

ON MyAlias.PrimarySecondary_ICD = RK_tblInjCodes.Code

order by MyAlias.Process_ID

My problem is that it now takes over 2 minutes (less than before though) to execute the query and I'm wondering if there's anything I can do to speed it up.

I've tried creating am index on a view (created from the select), but I don't have permissions. Neither is the view I'm selecting from above actually indexed which doesn't help either (our database team have a reason for not doing so), so I appear to be a little stuck on that issue. I've also tried dumping the output into a temp table rather than using a cte, but that's even slower.

Again any suggestions gratefully received.

Cheers