September 22, 2008 at 3:43 pm
Hi All
I have a problem I'd like to solve in SQL, but not sure if it's possible..so some ideas would be appreciated.
Problem is related to the healthcare domain. Patients can get classified using various codes, for example, let's say the hypertension diagnosis codes are ht1, ht2, ht3 and the diabetes codes are dm1, dm2, dm3, dm4. Patients normally get classified with a single code, so I'd like to pass the list of codes (may be as a comma separated list) to a UDF/stored procedure for further processing. First I need to identify patients who have multiple conditions - say the query is "Patients with hypertension and diabetes". Someone may think you can easily store all the hypertension codes in a table and the diabetes codes in another table and do a simple join on the 3 tables, but the problem is the codes that constitute the conditions can change which is why I want to pass the lists of conditions to the UDF. I'm looking for a fairly generic solution instead of a brute force type mechanism if there is one, cos you may want to check for patients with 3 or 4 or 'n' conditions as well. In terms of the data, I have is a PATIENTS table:
CREATE TABLE #PATIENTS (ID varchar(10), Code varchar(20))
INSERT INTO #PATIENTS (ID, Code)
SELECT 1 , 'p1', 'ht2' UNION ALL
SELECT 2 , 'p2', 'x1' UNION ALL
SELECT 3 , 'p3', 'x2' UNION ALL
SELECT 4 , 'p4', 'ht1' UNION ALL
SELECT 5 , 'p5', 'x3' UNION ALL
SELECT 6 , 'p1', 'dm3' UNION ALL
SELECT 6 , 'p6', 'ht2' UNION ALL
SELECT 7 , 'p4', 'dm1'
where 'x' is some random classification/diagnosis.
I'd like to pass to UDF (not sure how to):
"HT codes" - ht1, ht2, ht3, ht4
"DM codes" - dm1, dm2, dm3, dm4, dm5
So after executing the query I want p1 and p4 as the result set as they are the only patients who have both, diabetes and hypertension.
I do know that you can pass a comma separated list and then split the list, but in my case there could be multiple lists I'd like to pass ("HT codes" and "DM codes" for eg) in which case the UDF should do a ...IN list1 AND ...IN list2 type of a join.
Hope someone can suggest a sensible way of doing this cos I just can't come up with a solution! Thanks 🙂
September 22, 2008 at 4:06 pm
A few comments:
- trying to make this generic is going to force you to use a stored proc and not a function. You essentially can't do dynamic queries through T_SQL user-defined functions.
- I don't think you looked hard enough at using a criteria table, which would make it fairly easy to codify what you're trying to get at. Using a (permanent) table with a QueryID, listNum, ListValue combination of fields would allow you to create your lists on the fly, and only need to pass in the queryID you want to run.
- at that point, since you're talking about a variable number of lists to possibly check, you're into Dynamic SQL to do this.
If you wanted to specify the maximium number of lists you might have, then you could get away with not going dynamic, which might mean you could use a function to generate the results. That being said - depending on what you do with the patient list after that, it may not be desirable to set this up as a function (since functions like this would return a table variable, which would not tend to behave well if you plan on joining to it in an outer query).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 25, 2008 at 7:55 am
Matt has excellent points. You should take his advice and avoid this approach.
If you have to pass "list" of things, you should consider XML rather than splitting up delimited lists. The internal XML processing in SQL Server is pretty fast and you could manage your data in a structure that would allow multiple "sets" in a single structure. The down side is XML data is big and you may have all kinds of performance issues as your lists increase in size.
September 25, 2008 at 3:25 pm
Thanks guys. I wasn't exactly sure what Matt meant by a 'criteria' table though. The problem is the lists are created based on selected user inputs, which is why I wanted to pass them as a delimited parameter which can then be split. Using xml is an option I suppose, but this means I need to create the xml file just before making the server call right?
September 25, 2008 at 3:52 pm
I meant - something like a table where you load in the lists of values. For example, in the case you mentioned (where you're looking for patients that are both hypertensive and diabetic), you'd load up your table something like so.
The queryID is some ID you generate to identify the question/combination of criteria. Most likely a static number (here I used AAAAA as the queryID)
Table queryCrit
QueryID listID code
AAAAA l1 dm1
AAAAA l1 dm2
AAAAA l1 dm3
AAAAA l1 dm4
AAAAA l2 HT1
AAAAA l2 HT1
AAAAA l2 HT1
AAAAA l2 HT1
(in this case you're "using" a standard listID to determine your criteria. here L1 = diabetes, and L2 = hypertension).
In this case you'd pass in AAAAA as the queryID you want. The stored proc would then start from a "boiler plate query string" looking something like
Select patientlist.*
from patientlist
inner join patientconditions on patientlist.patientID=patientConditions.patientID
where conditioncode in (select code from querycriteria where listID='L1')
AND conditioncode in (select code from querycriteria where listID='L2')
and queryID=@queryID
You could then decide whether to hard code how many lists. If you diecide you need a variable number, then you build the query above with a variable number of AND's based on how many lists you have.
Does that help?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 25, 2008 at 3:57 pm
Also - for what it's worth. Since you did post in the 2008 forum, you might consider using the new SQL 2008 feature allowing you to build and pass a table parameter to your stored procedure. I don't know any more than it's available (I haven't tested that part in 2008 yet), but you can read about it here in BOL:
http://msdn.microsoft.com/en-us/library/ms187926.aspx
http://msdn.microsoft.com/en-us/library/bb510489.aspx
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 25, 2008 at 4:16 pm
Thanks for the quick feedback Matt. Your 1st suggestion certainly helps. The criteria table itself is not entirely static cos we want to be able to say "these are the codes that define hypertension", but that's not something that's going to happen often..so it's just a matter of simply updating the static tables if it does I suppose. Neat technique!
In terms of passing a table parameter to the stored procedure, the idea would be to have the table parameter's structure similar to that of the criteria table's right??
September 25, 2008 at 7:08 pm
thusi (9/25/2008)
Thanks for the quick feedback Matt. Your 1st suggestion certainly helps. The criteria table itself is not entirely static cos we want to be able to say "these are the codes that define hypertension", but that's not something that's going to happen often..so it's just a matter of simply updating the static tables if it does I suppose. Neat technique!In terms of passing a table parameter to the stored procedure, the idea would be to have the table parameter's structure similar to that of the criteria table's right??
Essentially - or something similar. As long as it's close enough to give you what you need to build the criteria.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 15, 2008 at 6:46 am
I have done this kind of thing before.
You are looking for patients with two disorders (in your example)
But your patients are assigned a DisorderSubLevel (h1, h2, h3)
I am assuming you have some sort of disorder heirarchy
It seems you should be searching the disorders you are interested in....
If you still want to pass h1, h2 etc
1. I use a Delimted list as parameter
2.I split this into a table of search parameters.
get #Param with list of h1,h2,h3 etc
3. Link patients to this, group by patients
having a count( distinct Disorder ) =
Count(distinct disorder ) in the parameter list.
----Prep-----------------------------------------------
CREATE TABLE #PATIENTS (ID varchar(10), Code varchar(20))
INSERT INTO #PATIENTS (ID, Code)
SELECT 'p1', 'ht2' UNION ALL
SELECT 'p2', 'x1' UNION ALL
SELECT 'p3', 'x2' UNION ALL
SELECT 'p4', 'ht1' UNION ALL
SELECT 'p5', 'x3' UNION ALL
SELECT 'p1', 'dm3' UNION ALL
SELECT 'p6', 'ht2' UNION ALL
SELECT 'p4', 'dm1'
Create Table #DisorderLevel( Code Varchar(20), Disorder Varchar(5) )
Insert into #DisorderLevel
Select 'ht1', 'Hyp' Union
Select 'ht2', 'Hyp' union
Select 'ht3', 'Hyp' union
Select 'ht4', 'Hyp' union
Select 'dm1', 'Dia' Union
Select 'dm2', 'Dia' union
Select 'dm3', 'Dia' union
Select 'dm4', 'Dia' union
Select 'm1', 'Mad' Union
Select 'm2', 'Mad' union
Select 'm3', 'Mad' union
Select 'm4', 'Mad'
----Split the list into a parameter table whish ends up looking like below----
Create Table #Parameter( Code Varchar(20), Disorder Varchar(5) )
Insert into #Parameter
Select * From #DisorderLevel Where Disorder <> 'Mad'
Select
p.ID
, Count( d.Disorder )
From #Patients as p
Inner join #Parameter as d
on d.Code = p.Code
Group by
p.ID
Having Count( d.Disorder ) =
(
Select Count( Distinct Disorder )
from #Parameter
)
October 16, 2008 at 8:58 am
Not sure if this is going to be extremely helpful, but it might reduce your dataset a little. Rather than listing 401.1,401.2,401.3, etc as hypertension, just look at the first three digits for hypertension. ICD-9 is all classified already, just use the general classifications where possible. Same with diabetes, 250.xx = diabetes.
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
October 17, 2008 at 8:00 am
See here for some guidance: http://www.sommarskog.se/arrays-in-sql.html. Not sure if this has been updated for 2008 though.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 17, 2008 at 10:37 pm
Article about how to pass 1, 2, and 3 dimensional arrays...
http://www.sqlservercentral.com/articles/T-SQL/63003/
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply