Monday, January 21, 2013 7:40 PMI 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.
Monday, January 21, 2013 7:49 PMA 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).
- Marked As Answer by Roman32 Tuesday, January 22, 2013 4:41 PM
Monday, January 21, 2013 9:06 PM
Something like this
create table code( code varchar(20) primary key ) create table code_group (code_group varchar(20) primary key) create table code_group_code ( code varchar(20) references code on delete cascade, code_group varchar(20) references code_group on delete cascade, constraint pk_code_group_code primary key (code, code_group) ) create index ix_code_group_code on code_group_code(code_group) insert into code values ('ht1'),('ht2'),('ht3'),('dm1'),('dm2'),('dm3'),('x1'),('x2'),('x3') insert into code_group values ('HT codes'),('DM codes') insert into code_group_code(code,code_group) values ('ht1','HT codes'),('ht2','HT codes'),('ht3','HT codes'), ('dm1','DM codes'),('dm2','DM codes'),('dm3','DM codes') go create table patient_code ( id varchar(10), code varchar(20) references code, constraint pk_patient_code primary key (id,code) ) create index ix_patient_code on patient_code(code) insert into patient_code(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' go --first a very non-generic query select id from patient_code pc where pc.code in ( select code from code_group_code where code_group = 'HT codes' ) intersect select id from patient_code pc where pc.code in ( select code from code_group_code where code_group = 'DM codes' ) --now a more dynamic one where you can pass a list of code groups declare @code_groups table (code_group varchar(20)); insert into @code_groups values ('DM codes'), ('HT codes') select p.id from patient_code p join code_group_code c on p.code = c.code where c.code_group in (select code_group from @code_groups) group by p.id having count(distinct c.code_group) = (select count(*) from @code_groups)