May 17, 2011 at 7:54 am
Hi
I have an SQL statement using a CASE statement which searches across 14 columns (named 'Diagnosis1stSecondary_ICD' to 'Diagnosis14thSecondary_IC'D) for the first occurrence of a code in the range 'V000' to 'Y98%' and selects that code into a column called 'PrimarySecondary_ICD2'. This works fine.
However, the query is slow and I have been experimenting with using COALESCE instead to select the relevant code into a column named 'PrimarySecondary_ICD'.
However, the COALESCE statement ignores the range of codes specified in the WHERE clause and merely extracts the first non-null code across the 14 columns specified above.
Any ideas why?
I've included the code below, which includes both the CASE statement and the COALESCE.
I have also attached a screen dump of the sample output - as you can see rows 3 and 4 under the column 'PrimarySecondary_ICD' show incorrect codes.
Select
MyAlias.Process_ID,
MyAlias.PCT,
PrimarySecondary_ICD,
PrimarySecondary_ICD2,
RK_tblInjCodes.Code,
RK_tblInjCodes.SecondaryGroupDesc as SecGroupDesc,
RK_tblInjCodes.PrimaryGroupDesc as PrimeGroupDesc,
MyAlias.SpellEnd,
MyAlias.SpellEndUK,
MyAlias.Fin_Year,
MyAlias.Year,
MyAlias.Month,
MyAlias.MthCHAR2
FROM
( SELECT
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 SpellEnd,
convert(varchar(10),AIMTC_ProviderSpell_End_Date, 103) as SpellEndUK,
coalesce
(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) as PrimarySecondary_ICD,
PrimarySecondary_ICD2 =
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 */
)MyAlias
inner join zag.analyst_sql_area.dbo.RK_tblInjCodes
ON MyAlias.PrimarySecondary_ICD2 = RK_tblInjCodes.Code
order by MyAlias.Process_ID
Any assistance on offer gratefully received.
Cheers
May 17, 2011 at 12:35 pm
It seems like you got trapped by a "semi-optimal" table design...
You might want to look into UNPIVOT to normalize the table. This would make the query much easier and most probably leading to a much better performance.
The best solution, however, would be a normalized table design to begin with...
If you need some help to rewrite that query please provide table def and some ready to use sample data as described in the first link in my signature together with your expected result based on those sample data.
May 17, 2011 at 2:38 pm
The coalesce and case statements are not equivelant. COALESCE will return the firt non-null value and your case statement is going to return the first code that does not fall between the range.
If any of the columns contain values that are not null - and not between that range, the coalesce will return it.
Additionally, your where clause is only checking for a code in the range that exists in at least one of those columns. So, let's say you have nothing in Diagnosis1stSecondary through Diagnosis10thSecondary, and Diagnosis11thSecondary has the value X330, and Diagnosis12thSecondary has V220.
The row is selected because it has a valid code - the case statement returns the valid code because it won't find any matching values until the 12th one. The coalesce fails because it returns the first non-null value (11th) which is not valid for the range.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 19, 2011 at 6:36 am
Hi Guys
Thanks for the comments - some really good stuff mentioned, which I was unaware of.
I have re-written my SQL as follows, but receive the error message "Msg 8155, Level 16, State 2, Line 93 No column name was specified for column 11 of 'MyAlias'"
I can't work out what the problem is.... When I double-click the error message it highlights this line of SQL:
inner join zag.analyst_sql_area.dbo.RK_tblInjCodes
Also, once the data has unpivoted, I will get more than one row of data per patient (this is patient data) if a patient has more than one code in the range 'V000' to 'Y98%', while what I need is only the first occurrence of the required code range across the cols (as was). i.e 1 row of data per patient with a single code returned.
Any further help gratefully received.
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,
MyAlias.Code,
RK_tblInjCodes.Code,
RK_tblInjCodes.SecondaryGroupDesc as SecGroupDesc,
RK_tblInjCodes.PrimaryGroupDesc as PrimeGroupDesc,
MyAlias.SpellEnd,
MyAlias.SpellEndUK,
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 SpellEnd,
convert(varchar(10),AIMTC_ProviderSpell_End_Date, 103) as SpellEndUK,
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,
left(Code.Code,3), Code.CodeType as Code
--Count (*) as number
FROM zag.abi.dbo.vw_APC_SEM_001
UNPIVOT (Code for CodeType in
(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)) as Code
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
Code between 'V000' and 'Y98%'
AND
Code not between 'X330' and 'X399'
AND
Code <> '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
inner join zag.analyst_sql_area.dbo.RK_tblInjCodes
ON MyAlias.Code = RK_tblInjCodes.Code
order by MyAlias.Process_ID
Cheers
May 19, 2011 at 10:32 am
The error most probably points to the column left(Code.Code,3) with a missing alias.
Regarding the first occurence issue: maybe it's possible to filter it out with a WHERE clause or another ROW_NUMBER.
But I'd need some test data to play around with it. It might also be because of the structure the query is written... I'm used to SS2K5 CTE's instead of subqueries by now.
Speaking of SS2K5: since you're using ROW_NUMBER() and UNPIVOT I assume you're not using SS2K or below (even though the forum you posted in indicate something different)...
May 20, 2011 at 7:57 am
Hi
Thanks for the reply. I'll have a play around with your suggestions and let you know how far I get that way. I'll post some data if I grind to a halt.
Also, you are correct, I'm posting on the wrong forum as I use SQL server 2008 - I obviously clicked the wrong link. I shall be more careful next time around.
Cheers
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply