| Author |
Topic |
|
thusi
Starting Member
25 Posts |
 Posted - 2008-09-22 : 08:10:06
|
| Hi AllI 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 list 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 which has ID, ClassificationCode.Sample data:PATIENTSp1, ht2p2, x1p3, x2p4, ht1,p5, x3p1, dm3p6, ht2p4, dm1where 'x' is some random diagnosis. Pass to UDF: "HT codes" - ht1, ht2, ht3, ht4"DM codes" - dm1, dm2, dm3, dm4, dm5So 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.Hope someone can suggest a sensible way of doing this cos I just can't come up with a solution! Thanks :) |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-09-22 : 09:45:50
|
He posted in SQL Server 2008.Make use of the new table datatype and send as parameter and join directly.
E 12°55'05.63"N 56°04'39.26" |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-09-22 : 09:52:13
|
quote:
Originally posted by Peso
He posted in SQL Server 2008.Make use of the new table datatype and send as parameter and join directly.
E 12°55'05.63"N 56°04'39.26"
yeah...sorry didnt notice it  |
|
|
|
thusi
Starting Member
25 Posts |
Posted - 2008-09-22 : 17:31:53
|
| Hi guysThanks for the replies, but there're a few issues. Yes, I can get the split string values into a table using several UDFs floating around on the web and then do a join easily, but my problem is more complex :p. Biggest issue is, I want to determine the tables dynamically cos I don't know how many tables I'll need to join. For instance, when there's a query "Patients with HT and DM", the parts that constitute the IN in sql comes for 2 lists and I want to make sure the patient has at least one code from each of the lists. So if you look at my original post, "HT Codes" and "DM Codes" and 'ht1,ht2', 'dm1,dm2' etc are things I'd like to pass into the UDF but then the UDF should have the intelligence to find out if IN is satisfied from both the lists. Hope the question makes more sense now. Thanks |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-09-23 : 00:51:08
|
quote:
Originally posted by thusi
Hi guysThanks for the replies, but there're a few issues. Yes, I can get the split string values into a table using several UDFs floating around on the web and then do a join easily, but my problem is more complex :p. Biggest issue is, I want to determine the tables dynamically cos I don't know how many tables I'll need to join. For instance, when there's a query "Patients with HT and DM", the parts that constitute the IN in sql comes for 2 lists and I want to make sure the patient has at least one code from each of the lists. So if you look at my original post, "HT Codes" and "DM Codes" and 'ht1,ht2', 'dm1,dm2' etc are things I'd like to pass into the UDF but then the UDF should have the intelligence to find out if IN is satisfied from both the lists. Hope the question makes more sense now. Thanks
do you pass both codes through same parameter or you have two parameters for passing two lists? |
|
|
|
thusi
Starting Member
25 Posts |
Posted - 2008-09-23 : 06:26:36
|
| Well, I don't mind either way..which is why I posted an open ended question. I haven't designed this feature yet, so open to suggestions. Just that there has to be some way to distinguish between the different lists, either at the server end by splitting a single parameter to the corresponding lists, or else by passing multiple parameters to the server; but the problem with the latter approach is I'm not sure if you can pass a variable number of arguments which is what I want..cos it's not necessarily two parameters always. Cheers |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-09-23 : 06:39:52
|
quote:
Originally posted by thusi
Well, I don't mind either way..which is why I posted an open ended question. I haven't designed this feature yet, so open to suggestions. Just that there has to be some way to distinguish between the different lists, either at the server end by splitting a single parameter to the corresponding lists, or else by passing multiple parameters to the server; but the problem with the latter approach is I'm not sure if you can pass a variable number of arguments which is what I want..cos it's not necessarily two parameters always. Cheers
better to pass it as two parameters. Then use condition value IN (SELECT val from dbo.ParseValues (@list1)) AND value IN (SELECT val from dbo.ParseValues (@list2)) to get items having at least one value from both list |
|
|
|
thusi
Starting Member
25 Posts |
Posted - 2008-09-23 : 08:23:48
|
| Yeah, I guess I'll have to implement it that way. Most often it's going to be only 2 lists, but if we need to have 3, I guess it's a matter of including another parameter and passing NULL or something where the 3rd parameter is not required. Thanks for the suggestion :-) |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-09-23 : 08:37:44
|
quote:
Originally posted by thusi
Yeah, I guess I'll have to implement it that way. Most often it's going to be only 2 lists, but if we need to have 3, I guess it's a matter of including another parameter and passing NULL or something where the 3rd parameter is not required. Thanks for the suggestion :-)
isnt the number of lists certain at complie time?will it be varying dynamically? |
|
|
|
thusi
Starting Member
25 Posts |
Posted - 2008-09-23 : 16:35:29
|
| Ideally it should be able to vary dynamically. Want to let users select multiple constrains (which translate to multiple 'lists' we are talking about here) from the GUI, but restricting to 2 or 3 is not a big deal. Will make implementation a lot easier I think than making it truly dynamic. |
|
|
|
|
Variable #of arguments to UDF/stored procedure