RE: Select records with different values in the same column

SSC Guru

Points: 186127

August 28, 2014 at 7:41 am

Lots of options for this:

;WITH PrimaryDiagList AS (SELECT * FROM (VALUES

('402.01'),('402.11'),('402.91'),('404.01'),('404.03'),('404.11'),('404.13'),('404.91'),('404.93'),('428.0'),('428.10'),('428.20'),

('428.21'),('428.22'),('428.23'),('428.30'),('428.31'),('428.32'),('428.33'),('428.40'),('428.41'),('428.42'),('428.43'),('428.90')

) d (diagnosis))

SELECT t.*

FROM dbo.TEST t

INNER JOIN PrimaryDiagList p

ON p.diagnosis = t.diagnosis

WHERE t.DiagnosisSeqID = '1'

AND EXISTS (

SELECT 1

FROM dbo.TEST ti

WHERE ti.UnitNumber = t.UnitNumber

AND ti.diagnosis = '427.31'

AND ti.DiagnosisSeqID > '1'

)

;WITH PrimaryDiagList AS (SELECT * FROM (VALUES

('402.01'),('402.11'),('402.91'),('404.01'),('404.03'),('404.11'),('404.13'),('404.91'),('404.93'),('428.0'),('428.10'),('428.20'),

('428.21'),('428.22'),('428.23'),('428.30'),('428.31'),('428.32'),('428.33'),('428.40'),('428.41'),('428.42'),('428.43'),('428.90')

) d (diagnosis))

SELECT t.UnitNumber

FROM dbo.TEST t

WHERE (t.DiagnosisSeqID = '1' AND EXISTS (SELECT 1 FROM PrimaryDiagList p WHERE p.diagnosis = t.diagnosis))

OR (t.DiagnosisSeqID > '1' AND t.diagnosis = '427.31')

GROUP BY t.UnitNumber

HAVING MIN(t.DiagnosisSeqID) <> MAX(t.DiagnosisSeqID)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden