Keep All records with same FK if at least 1 matches criteria from second column 1

[waubain]

Here is the simplified structure of my data. I have Access experience, but I have been upgraded to the our corporate database and am using SQL Management Server 2012 so I am feeling a little lost.

[tt]
PatientSID	ICDCode
1		332.0
1		332.0	
1		709.8
2		332.0
3		333.9
3		250.6
[/tt]

I am trying to keep all records from PatientSID as long as at least 1 record has an ICDCode of 332.0
In the above data I want to include the 3 records for PatientSID 1 and the 1 record from PatientSID 2, but no records for PatientSID 3.

I have looked at various GROUPing, RANK, ROW_NUMBER statements, but I do not think any of these will work. I am thinking I might need to rethink my JOINS and have a RIGHT JOIN after first creating a subquery of only ICDCode of 332.0, but I am having trouble thinking that through. Here is the SQL that has all the information, but not filtered.

USE LSV

	SELECT
		t.PatientSID
		,i.ICDCode
		--,p.PatientName
		--,p.PatientSSN
		--,t.EntryDateTime
		--,d.TIUDocumentDefinition
		--,i.ICDSID
		--,i.ICDDescription
	
	FROM

		TIU.TIUDocument		AS t

		LEFT JOIN   dim.TIUDocumentDefinition	AS d	ON t.TIUDocumentDefinitionSID = d.TIUDocumentDefinitionSID
		LEFT JOIN   SPatient.SPatient		AS p	ON t.PatientSID = p.PatientSID
		LEFT JOIN   Outpat.VDiagnosis		AS v	ON t.VisitSID = v.VisitSID
		LEFT JOIN   dim.ICD			AS i	ON v.ICDSID = i.ICDSID 

	WHERE

		t.EntryDateTime BETWEEN '2002/1/1' AND '2013/12/16'
		AND d.TIUDocumentDefinition LIKE 'NEUROLOGY MOVEMENT%'

Thank you for any suggestions.

You don't know what you don't know...

 

[bborissov]

SELECT YourTable.*
FROM YourTable
INNER JOIN (SELECT DISTINCT PatientSID FROM YourTable WHERE ICDCode = 332.0) Tbl1
      ON YourTable.PatientSID = Tbl1.PatientSID

Borislav Borissov
VFP9 SP2, SQL Server

 

[gmmastros]

Boris's query should give you all the data for patients with at least one icd code = 332.0.

To find rows for patients without that code...

Select	PatientSID
From    YourTable
Group By PatientSID
Having Count(Case When ICDCode = 332.0 Then 1 End) = 0

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[waubain]

Borislav: Thank you for the reply. I apologize for my lack or understanding, but I am unclear what YourTable is referring. The information I need comes from several tables that are joined as written in my query. I saved my above query into a temptable I named #cohort and then ran your query against that query, but I get an error

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

Which points to the first statement SELECT #cohort.* in your code.

USE LSV

IF OBJECT_ID ('TEMPDB..#cohort') IS NOT NULL DROP TABLE #cohort

	SELECT
		t.PatientSID
		,p.PatientName
		,p.PatientSSN
		,t.EntryDateTime
		,d.TIUDocumentDefinition
		,i.ICDCode
		,i.ICDSID
		,i.ICDDescription

INTO #cohort
	
	FROM

		TIU.TIUDocument		AS t

		LEFT JOIN   dim.TIUDocumentDefinition	AS d	ON t.TIUDocumentDefinitionSID = d.TIUDocumentDefinitionSID
		LEFT JOIN   SPatient.SPatient		AS p	ON t.PatientSID = p.PatientSID
		LEFT JOIN   Outpat.VDiagnosis		AS v	ON t.VisitSID = v.VisitSID
		LEFT JOIN   dim.ICD			AS i	ON v.ICDSID = i.ICDSID 

	WHERE

		t.EntryDateTime BETWEEN '2002/1/1' AND '2013/12/16'
		AND d.TIUDocumentDefinition LIKE 'IC/NEUROLOGY MOVEMENT%'

	
	SELECT #cohort.* 
	FROM #cohort
	INNER JOIN (SELECT DISTINCT PatientSID FROM #cohort WHERE ICDCode = 332.0) Tbl1
      ON #cohort.PatientSID = Tbl1.PatientSID

I will study this more tonight, but at this stage I cannot fully appreciate your SQL statements. Maybe I have to start with your query and add to it based on my other column needs.

George: Thank you for your reply and teaching.



You don't know what you don't know...

 

[bborissov]

Try this:

SELECT TIUDocument.PatientSID
      ,SPatient.PatientName
      ,SPatient.PatientSSN
      ,TIUDocument.EntryDateTime
      ,TIUDocumentDefinition.TIUDocumentDefinition
      ,ICD.ICDCode
      ,ICD.ICDSID
      ,ICD.ICDDescription
FROM TIU.TIUDocument                                                    TIUDocument
 INNER JOIN dim.TIUDocumentDefinition                                   TIUDocumentDefinition ON TIUDocument.TIUDocumentDefinitionSID = TIUDocumentDefinition.TIUDocumentDefinitionSID
 LEFT  JOIN SPatient.SPatient                                           SPatient              ON TIUDocument.PatientSID = SPatient.PatientSID
 INNER JOIN Outpat.VDiagnosis                                           VDiagnosis            ON TIUDocument.VisitSID = VDiagnosis.VisitSID
 INNER JOIN dim.ICD                                                     ICD                   ON VDiagnosis.ICDSID = ICD.ICDSID 
 INNER JOIN (SELECT DISTINCT ICDSID FROM dim.ICD WHERE ICDCode = 332.0) Tbl1                  ON VDiagnosis.ICDSID = ICD.ICDSID 

WHERE TIUDocument.EntryDateTime BETWEEN '20020101' AND '20131216'
  AND TIUDocumentDefinition.TIUDocumentDefinition LIKE 'IC/NEUROLOGY MOVEMENT%'

Personally I hate one letter aliases.
Changed LEFT to INNER joins, the way query is written there is no difference.
If you get that error again, it will be because maybe the type of ICDCode is varchar and some of the entries there are not convertible to numeric.
Then just change

...WHERE ICDCode = 332.0

-- to
...WHERE ICDCode = '332.0'

Borislav Borissov
VFP9 SP2, SQL Server

 

[waubain]

Borislav,
Thank you for writing it out for me. I will take note of your personal dislike for one letter aliases. When you do not know anything and are following by example, it is difficult to know the difference between SHOULD and COULD. Spelling it all out does make it clearer.

Thank you again.

You don't know what you don't know...