Avatar of jul_to_cool
jul_to_cool
Flag for United States of America asked on

Distinct Count in SQL with conditions

Hi All,

I need to identify the PtID that have 2+ unique ORDER_MED_ID'S and the DISCON_TIME is null, I'll need to display the number of unique ORDER_MED_ID's that meet the condition and then limit the query to people that have 2+ unique ORDER_MED_ID'S that meet the condition.   How can I accomplish this?

select
orm.ORDER_MED_ID
, orm.DISCON_TIME

from PAT_ENC pae
inner join ORDER_MED orm ON pae.PAT_ENC_CSN_ID=orm.PAT_ENC_CSN_ID

where
#ofmeds >=2

From the example below, PtID 1234 and 3456 meet the condition.

The results look like this:
PtID                    ORDER_MED_ID               DISCON_TIME

1234                    0101233                          
1234                    0101234                          
1234                    0101235                          
1234                    0101236
2345                    0101237
3456                    0101238
3456                    0101239
3456                    0101240                           2015-07-20
4567                    0101241                           2015-07-20
Microsoft SQL Server

Avatar of undefined
Last Comment
jul_to_cool

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
savic7uk

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
jul_to_cool

ASKER
Thanks!  That's what I needed!
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy