TSQL query problems

Question

Post reply

TSQL query problems

J M-314995

SSCrazy

Points: 2975
More actions
March 5, 2010 at 4:40 pm

#217637

I'm trying to write a query to obtain 1st, 2nd, 3rd and 4th secondary diagnosis (dx) codes.
The table they are stored in kind of looks like this for one account...
visit_internal_id icd9_sequence_no icd9_internal_id ICD9_rank_no ICD9_diag_ty
1357 12299 2157 1 P
1357 12300 3544 0 A
1357 12301 3210 2 S
1357 12302 3099 2 S
1357 12303 2753 2 S
1357 12304 2999 2 S
Basically, this would be one account that has 1 primary dx (indicated with a P), 1 admitting dx (indicated with an A), and 4 secondary dx codes.
I'm being asked to pull the first 4 secondary dx codes into one record on the report I'm writing. I've seen 25 secondary dx's before on an account but again, I'm only being asked to pull the first 4 if they exist. If the ICD9_rank_no field continued with with 3, 4, 5, 6 and so on, I'd have no problems writing this report. I'm thinking I'm going to have capture the range in the icd9_sequence_no field and figure out how to see which one is the 3rd highest for the 1st secondary dx and the 4th highest for the 2nd secondary dx etc.
I'm linking other fields in other tables that contain actual information according to the internal id's. So the example below isn't going to match the codes above because the codes above would be internal id's. These are a few of the columns I'd be after in my report.
Visit_id Name Primary_dx Secondary_dx1 Secondary_dx2 Secondary_dx3
999011 Glick, Syd 2112 2124 2122 2135
As of now, I'm using a sub query for each dx code pull. The thing is, as you can tell by how the table is structured above, you'll know when I'm getting the following error.
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
I can easily pull the primary dx because each account only has one. It's all the secondary dx's I'm having problems with.
Thanks in advance.
John

Viewing 8 posts - 1 through 7 (of 7 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

You might want to use ROW_NUMBER() together with a CrossTab query as described in the related link in my signature.

If you need further assistance please provide table structure and sample data as shown in the first link in my sig.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 2

The methods shown in the "CrossTabs" and "Dynamic Crosstabs" links of Lutz's signature line would probably be just what the doctor ordered for this problem.;-)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

J M-314995 SSCrazy Points: 2975 More actions · Answer 3

lmu92 (3/5/2010)
You might want to use ROW_NUMBER() together with a CrossTab query as described in the related link in my signature.
If you need further assistance please provide table structure and sample data as shown in the first link in my sig.

I've actually tried ROW_NUMBER() before posting my original question but I was getting the same error I mentioned. I've googled for ROW_NUMBER() and I'm not seeing any examples of how I need to use it. I'm actually using ROW_NUMBER() in one of my other reports but I can't make sense of how to use it in this report.

I didn't know what CrossTab was until I saw an example and I've been using that method a lot in my report queries. CrossTab is not helping me either because I never know what number range the icd9_seq_no will be for each visit number. The example given when I clicked on the link in your sig, the numbers are all given....and I'll never know what the numbers are in my query. I have to somehow capture that number range in the icd9_seq_no field (the top 4 numbers) and each of the 4 sub querys is going to have to know which number to use to provide the correct dx code. The first subquery would use the top or lowest number, the second would have to use the second lowest and so on.

You're asking me for what sounds like a lot of information. I've been accused of giving too much information sometimes in my posts. I thought I had given a little too much as it is but I'll try and work on sample data to give you and I'll try and scale down my query a lot keeping all the relevant information....it's pretty long.

Thanks,

John

J M-314995 SSCrazy Points: 2975 More actions · Answer 4

Jeff Moden (3/5/2010)
The methods shown in the "CrossTabs" and "Dynamic Crosstabs" links of Lutz's signature line would probably be just what the doctor ordered for this problem.;-)

I did not look at the Dynamic CrossTabs link in the sig...just the CrossTabs link. I'll give that other link a read over...

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 5

There are two distinct stages to this problem, data selection, then transformation. This query makes a start on data selection based on Lutz' suggestion of using ROW_NUMBER():

DROP table #Accounts

create table #Accounts (visit_internal_id INT, icd9_sequence_no INT, icd9_internal_id INT, ICD9_rank_no TINYINT, ICD9_diag_ty CHAR(1))

insert into #Accounts (visit_internal_id, icd9_sequence_no, icd9_internal_id, ICD9_rank_no, ICD9_diag_ty)

SELECT 1357, 12299, 2157, 1, 'P' UNION ALL

SELECT 1357, 12300, 3544, 0, 'A' UNION ALL

SELECT 1357, 12301, 3210, 2, 'S' UNION ALL

SELECT 1357, 12302, 3099, 2, 'S' UNION ALL

SELECT 1357, 12303, 2753, 2, 'S' UNION ALL

SELECT 1357, 12304, 2999, 2, 'S'

SELECT Rowthing, visit_internal_id, icd9_sequence_no, icd9_internal_id, ICD9_rank_no, ICD9_diag_ty

FROM (

SELECT Rowthing = ROW_NUMBER() OVER(PARTITION BY visit_internal_id, ICD9_diag_ty ORDER BY visit_internal_id, icd9_internal_id),

visit_internal_id, icd9_sequence_no, icd9_internal_id, ICD9_rank_no, ICD9_diag_ty

FROM #Accounts

) d

WHERE ICD9_diag_ty = 'S' AND Rowthing < 5

Have a look at the full query and also the inner part, it may not work correctly but it will point you in the right direction.

I had to set up your sample data as a temp table in order to create and test the query, can you extend the data range please? A few more secondaries, perhaps another 2 accounts should do it.

Cheers

ChrisM

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

LutzM SSC Guru Points: 107049 More actions · Answer 6

The reason for me not to provide any code (yet) is the simple lack of sample data.

The brief sample you posted doesn't match your expected result set.

If you could provide some sample data including matching result set it would be a lot easier for us to provide you with tested code snippets.