Help with case select SQL query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Help with case select SQL query

Author

Topic

ckedwards65
Starting Member

4 Posts

Posted - 2013-06-07 : 18:16:37

I am really stuck. I have been using SQL for awhile, but only for surface level stuff and I have not been able to figure out how to get what I need done. I would appreciate any help I can get on getting this query to work. Even if it is just to point me to a website or give me some ideas on how I need to code this.

Here is the scenario

There are two tables

patient and labresults

key between two tables is idpatient field

there are only two fields in the table that I am concerned with

Patd - idpatient
labresults - idpatient and idtl

I need to produce a list of patients that have at least one record in the labresults table for 5 different tests. the idtl values are listed below.

idtl nametest
there is only 1 idtl value for these tests
1159 URR
1683 Height CM
1714 WEIGHT-POST (KG)[/red]
2343 HCT CALC (HGBX3)

this test can have the following values
1730 KT/V CMH
1744 KT/V TOTAL STD
1747 KT/V TOTAL FRIIS
1760 KT/V BOY SHORT
1761 KT/V BOY TALL
1762 KT/V GIRLS SHORT
1763 KT/V GIRL TALL
1780 spKt/V Total
1828 spKdt/V Dialysis
1832 stdKT/V Total
2838 KT/V Total PD
2848 KT/V Total PD

this is what I have, but it is still resulting in multiple records being generated for each patient

Select distinct p.idpatient
            ,(Case When c.idtl = 1159 Then c.idtl End )
            ,(Case When c.idtl = 1683 Then c.idtl End )
            ,(Case When c.idtl = 2423 Then c.idtl End )
            ,(Case When c.idtl = 1714 Then c.idtl End )
            ,(Case When c.idtl in (2848,1832,1828,1780,1763,1762,1761,1760,1747,1744,1730) Then c.idtl End )
            FROM patd p
join close_results c
on p.idpatient=c.idpatient

But the result is
idpatient urr hgt hct wgt ktv
11251 1159 NULL NULL NULL NULL
11251 NULL 1683 NULL NULL NULL
11251 NULL NULL NULL 1714 NULL
11251 NULL NULL NULL NULL NULL
11251 NULL NULL 2343 NULL 1780

What I need it to be is this:
idpatient urr hgt hct wgt ktv
11251 1159 1683 2343 1714 1780

And the row should only display i all of the five test fields have a value.

I will really appreciate any help I can get with this. I am stuck.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-07 : 18:25:33

Try modifying the query like shown below:

Select distinct p.idpatient
            ,MAX(Case When c.idtl = 1159 Then c.idtl End )
            ,MAX(Case When c.idtl = 1683 Then c.idtl End )
            ,MAX(Case When c.idtl = 2423 Then c.idtl End )
            ,MAX(Case When c.idtl = 1714 Then c.idtl End )
            ,MAX(Case When c.idtl in (2848,1832,1828,1780,1763,1762,1761,1760,1747,1744,1730) Then c.idtl End )
            FROM patd p
join close_results c
on p.idpatient=c.idpatient
GROUP BY p.idpatient

I did not quite follow the logic you are trying to implement, but if you are just interested in patients who have done at least 5 different tests, then the following would do:

SELECT
	idpatient
FROM
	close_results 
GROUP BY
	idpatient
HAVING
	COUNT(DISTINCT idtl) >= 5

If you need the details of the tests etc., you can then join this result to the close_results table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources