Select records with different values in the same column

Question

Post reply

Select records with different values in the same column

NineIron

SSChampion

Points: 12536
More actions
August 27, 2014 at 12:01 pm

#290166

I need to get a list of patients that have a primary diagnosis, DiagnosisSeqID=1, in this range, ('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') and have a secondary diagnosis, DiagnosisSeqID>1, of 427.31. Any thoughts?
create table dbo.TEST
(UnitNumber varchar(8),
AdmitDateTime datetime,
DischargeDateTime datetime,
DiagnosisSeqID varchar(2),
Diagnosis varchar(6)
)
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '000449','2014-05-18 06:14:00.000','2014-05-20 17:00:00.000','1','428.23')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '000449','2014-05-18 06:14:00.000','2014-05-20 17:00:00.000','2','427.31')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '000449','2014-05-18 06:14:00.000','2014-05-20 17:00:00.000','3','250')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '000449','2014-05-18 06:14:00.000','2014-05-20 17:00:00.000','4','428')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '000449','2014-05-18 06:14:00.000','2014-05-20 17:00:00.000','5','401.9')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '000449','2014-05-18 06:14:00.000','2014-05-20 17:00:00.000','6','272.4')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '000449','2014-05-18 06:14:00.000','2014-05-20 17:00:00.000','7','424')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '000449','2014-05-18 06:14:00.000','2014-05-20 17:00:00.000','8','V53.31')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '002611','2013-11-06 08:26:00.000','2013-11-09 11:40:00.000','1','428.33')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '002611','2013-11-06 08:26:00.000','2013-11-09 11:40:00.000','2','585.6')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '002611','2013-11-06 08:26:00.000','2013-11-09 11:40:00.000','3','411.89')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '002611','2013-11-06 08:26:00.000','2013-11-09 11:40:00.000','4','403.01')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '002611','2013-11-06 08:26:00.000','2013-11-09 11:40:00.000','5','250.4')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '002611','2013-11-06 08:26:00.000','2013-11-09 11:40:00.000','6','280')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '002611','2013-11-06 08:26:00.000','2013-11-09 11:40:00.000','7','578.1')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '002611','2013-11-06 08:26:00.000','2013-11-09 11:40:00.000','8','428')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '002611','2013-11-06 08:26:00.000','2013-11-09 11:40:00.000','9','530.81')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '002611','2013-11-06 08:26:00.000','2013-11-09 11:40:00.000','10','493.9')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '002611','2013-11-06 08:26:00.000','2013-11-09 11:40:00.000','11','272.4')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '002611','2013-11-06 08:26:00.000','2013-11-09 11:40:00.000','12','250.6')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '002611','2013-11-06 08:26:00.000','2013-11-09 11:40:00.000','13','357.2')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '002611','2013-11-06 08:26:00.000','2013-11-09 11:40:00.000','14','583.81')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '002611','2013-11-06 08:26:00.000','2013-11-09 11:40:00.000','15','535.5')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '002611','2013-11-06 08:26:00.000','2013-11-09 11:40:00.000','16','250.5')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '002611','2013-11-06 08:26:00.000','2013-11-09 11:40:00.000','17','362.01')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '002611','2013-11-06 08:26:00.000','2013-11-09 11:40:00.000','18','305.1')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '002611','2013-11-06 08:26:00.000','2013-11-09 11:40:00.000','19','V12.51')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '002611','2013-11-06 08:26:00.000','2013-11-09 11:40:00.000','20','V12.55')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '002611','2013-11-06 08:26:00.000','2013-11-09 11:40:00.000','21','V58.61')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '002611','2013-11-06 08:26:00.000','2013-11-09 11:40:00.000','22','V45.11')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '002611','2013-11-06 08:26:00.000','2013-11-09 11:40:00.000','23','V10.42')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '002611','2013-11-06 08:26:00.000','2013-11-09 11:40:00.000','24','V12.54')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '002611','2013-11-06 08:26:00.000','2013-11-09 11:40:00.000','25','V58.67')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '003137','2013-11-20 16:33:00.000','2013-11-25 13:20:00.000','1','428.43')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '003137','2013-11-20 16:33:00.000','2013-11-25 13:20:00.000','2','491.21')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '003137','2013-11-20 16:33:00.000','2013-11-25 13:20:00.000','3','70.7')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '003137','2013-11-20 16:33:00.000','2013-11-25 13:20:00.000','4','410.72')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '003137','2013-11-20 16:33:00.000','2013-11-25 13:20:00.000','5','250.02')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '003137','2013-11-20 16:33:00.000','2013-11-25 13:20:00.000','6','304')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '003137','2013-11-20 16:33:00.000','2013-11-25 13:20:00.000','7','428')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '003137','2013-11-20 16:33:00.000','2013-11-25 13:20:00.000','8','414.01')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '003137','2013-11-20 16:33:00.000','2013-11-25 13:20:00.000','9','401.9')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '003137','2013-11-20 16:33:00.000','2013-11-25 13:20:00.000','10','305.9')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '003137','2013-11-20 16:33:00.000','2013-11-25 13:20:00.000','11','V12.54')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '003137','2013-11-20 16:33:00.000','2013-11-25 13:20:00.000','12','V45.82')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '003137','2013-11-20 16:33:00.000','2013-11-25 13:20:00.000','13','V15.81')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '003137','2013-11-20 16:33:00.000','2013-11-25 13:20:00.000','14','V10.05')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '007899','2014-01-17 09:45:00.000','2014-01-18 14:30:00.000','1','428.33')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '007899','2014-01-17 09:45:00.000','2014-01-18 14:30:00.000','2','496')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '007899','2014-01-17 09:45:00.000','2014-01-18 14:30:00.000','3','250')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '007899','2014-01-17 09:45:00.000','2014-01-18 14:30:00.000','4','428')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '007899','2014-01-17 09:45:00.000','2014-01-18 14:30:00.000','5','401.9')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '007899','2014-01-17 09:45:00.000','2014-01-18 14:30:00.000','6','272.4')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '007899','2014-01-17 09:45:00.000','2014-01-18 14:30:00.000','7','338.29')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '007899','2014-01-17 09:45:00.000','2014-01-18 14:30:00.000','8','724.5')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '007899','2014-01-17 09:45:00.000','2014-01-18 14:30:00.000','9','242')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '007899','2014-01-17 09:45:00.000','2014-01-18 14:30:00.000','10','V15.82')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '007899','2014-01-17 09:45:00.000','2014-01-18 14:30:00.000','11','V58.66')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '010684','2013-10-27 10:10:00.000','2013-10-30 15:10:00.000','1','428.43')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '010684','2013-10-27 10:10:00.000','2013-10-30 15:10:00.000','2','486')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '010684','2013-10-27 10:10:00.000','2013-10-30 15:10:00.000','3','496')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '010684','2013-10-27 10:10:00.000','2013-10-30 15:10:00.000','4','427.31')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '010684','2013-10-27 10:10:00.000','2013-10-30 15:10:00.000','5','585.3')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '010684','2013-10-27 10:10:00.000','2013-10-30 15:10:00.000','6','998.59')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '010684','2013-10-27 10:10:00.000','2013-10-30 15:10:00.000','7','V45.81')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '010684','2013-10-27 10:10:00.000','2013-10-30 15:10:00.000','8','414.01')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '010684','2013-10-27 10:10:00.000','2013-10-30 15:10:00.000','9','272')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '010684','2013-10-27 10:10:00.000','2013-10-30 15:10:00.000','10','428')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '010684','2013-10-27 10:10:00.000','2013-10-30 15:10:00.000','11','300')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '010684','2013-10-27 10:10:00.000','2013-10-30 15:10:00.000','12','311')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '010684','2013-10-27 10:10:00.000','2013-10-30 15:10:00.000','13','790.92')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '010684','2013-10-27 10:10:00.000','2013-10-30 15:10:00.000','14','786.59')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '010684','2013-10-27 10:10:00.000','2013-10-30 15:10:00.000','15','E934.2')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '010684','2013-10-27 10:10:00.000','2013-10-30 15:10:00.000','16','403.9')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '010684','2013-10-27 10:10:00.000','2013-10-30 15:10:00.000','17','E878.2')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '010684','2013-10-27 10:10:00.000','2013-10-30 15:10:00.000','18','E849.7')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '010684','2013-10-27 10:10:00.000','2013-10-30 15:10:00.000','19','V43.65')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '010684','2013-10-27 10:10:00.000','2013-10-30 15:10:00.000','20','V58.61')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '010684','2014-08-14 06:08:00.000','2014-08-17 14:35:00.000','1','428.43')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '010684','2014-08-14 06:08:00.000','2014-08-17 14:35:00.000','2','584.9')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '010684','2014-08-14 06:08:00.000','2014-08-17 14:35:00.000','3','416.8')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '010684','2014-08-14 06:08:00.000','2014-08-17 14:35:00.000','4','427.31')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '010684','2014-08-14 06:08:00.000','2014-08-17 14:35:00.000','5','397')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '010684','2014-08-14 06:08:00.000','2014-08-17 14:35:00.000','6','530.81')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '010684','2014-08-14 06:08:00.000','2014-08-17 14:35:00.000','7','414')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '010684','2014-08-14 06:08:00.000','2014-08-17 14:35:00.000','8','496')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '010684','2014-08-14 06:08:00.000','2014-08-17 14:35:00.000','9','424')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '010684','2014-08-14 06:08:00.000','2014-08-17 14:35:00.000','10','285.9')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '010684','2014-08-14 06:08:00.000','2014-08-17 14:35:00.000','11','585.3')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '010684','2014-08-14 06:08:00.000','2014-08-17 14:35:00.000','12','403.9')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '010684','2014-08-14 06:08:00.000','2014-08-17 14:35:00.000','13','272.4')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '010684','2014-08-14 06:08:00.000','2014-08-17 14:35:00.000','14','428')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '010684','2014-08-14 06:08:00.000','2014-08-17 14:35:00.000','15','300')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '010684','2014-08-14 06:08:00.000','2014-08-17 14:35:00.000','16','780.4')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '010684','2014-08-14 06:08:00.000','2014-08-17 14:35:00.000','17','V45.01')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '010684','2014-08-14 06:08:00.000','2014-08-17 14:35:00.000','18','412')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '010684','2014-08-14 06:08:00.000','2014-08-17 14:35:00.000','19','V45.81')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '010684','2014-08-14 06:08:00.000','2014-08-17 14:35:00.000','20','V43.65')

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 1

could you please confirm your expected results based on the sample data provided....

ps..thanks for the set up script...makes life so much easier.

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

ScottPletcher SSC Guru Points: 101101 More actions · Answer 2

I guess "UnitNumber" is the "Patient".

SELECT UnitNumber,

MAX(CASE WHEN DiagnosisSeqID = 1 THEN Diagnosis ELSE '' END) AS Primary_Diagnosis,

'427.31' AS Seconday_Diagnosis_Match

FROM test t

GROUP BY UnitNumber

HAVING

MAX(CASE WHEN DiagnosisSeqID = 1 AND Diagnosis IN (

'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' ) THEN 1 ELSE 0 END) = 1 AND

MAX(CASE WHEN DiagnosisSeqID > 1 AND Diagnosis = '427.31' THEN 1 ELSE 0 END) = 1

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

rxm119528 SSCrazy Points: 2960 More actions · Answer 3

As per my understanding of the requirment

with CTE AS(select * from dbo.test where diagnosis = '427.31' and DiagnosisSeqID>1

union all

select * from dbo.test

where DiagnosisSeqID = 1 and diagnosis in

('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'))

select * from CTE

where unitnumber in (select distinct unitnumber from CTE

group by unitnumber having COUNT(*) > 1)

order by unitnumber,DiagnosisSeqID

NineIron SSChampion Points: 12536 More actions · Answer 4

NineIron

SSChampion

Points: 12536

August 28, 2014 at 4:02 am

#1741587

Thanx for the solution

NineIron SSChampion Points: 12536 More actions · Answer 5

NineIron

SSChampion

Points: 12536

August 28, 2014 at 4:02 am

#1741588

Thanx for the solution

rxm119528 SSCrazy Points: 2960 More actions · Answer 6

rxm119528 (8/27/2014)
As per my understanding of the requirment
with CTE AS(select * from dbo.test where diagnosis = '427.31' and DiagnosisSeqID>1
union all
select * from dbo.test
where DiagnosisSeqID = 1 and diagnosis in
('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'))
select * from CTE
where unitnumber in (select distinct unitnumber from CTE
group by unitnumber having COUNT(*) > 1)
order by unitnumber,DiagnosisSeqID

My code will fail if you enter the data

insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '123456','2014-05-18 06:14:00.000','2014-05-20 17:00:00.000','2','427.31')

insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, DiagnosisSeqID, Diagnosis) values( '123456','2014-05-18 06:14:00.000','2014-05-20 17:00:00.000','4','427.31')

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

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