Calculating if a patient in a table has two unique rows with specific entries

CMCITD
CMCITD used Ask the Experts™
on
I want a list of patients that take 2 (Or more) medication types at the same time.  The table I have;

 

Name                     Benzos____________Opioids___________Medication

Patient A                 Y                               N                         Xanax

Patient B                 Y                               N                         Xanax

Patient C                 Y                               N                         Xanax

Patient C                 N                              Y                         Lortab

Patient D                N                               Y                          Lortab

Patient E                Y                                N                         Xanax

Patient E                N                               Y                          Hydrocodone

 

 

Ideally, my visual would only display Patient C and Patient E (Those patients that had both a Benzo and Opioid medication, although different rows).  I can't group by--as that will break the rest of my report.  If I try a new Conditional Column (Or custom column with an IF statement), since it evaluates row by row without grouping by Patient--I end up with the same issue.  Thanks for any help!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Software Team Lead
Commented:
try customize this:

;with yourTable as
(
	Select 'Patient A' Name, 'Y' Benzos, 'N' Opioids, 'Xanax' Medication union
	Select 'Patient B' Name, 'Y' Benzos, 'N' Opioids, 'Xanax' Medication union
	Select 'Patient C' Name, 'Y' Benzos, 'N' Opioids, 'Xanax' Medication union
	Select 'Patient C' Name, 'N' Benzos, 'Y' Opioids, 'Lortab' Medication union
	Select 'Patient D' Name, 'N' Benzos, 'Y' Opioids, 'Lortab' Medication union
	Select 'Patient E' Name, 'Y' Benzos, 'N' Opioids, 'Xanax' Medication union
	Select 'Patient E' Name, 'N' Benzos, 'Y' Opioids, 'Hydrocodone' Medication
)
Select a.* from yourTable a
inner join
(
	Select Name from yourTable where Benzos = 'Y' or Opioids = 'Y'
	Group By Name
	Having Count(Name) = 2
) b on a.Name = b.Name
Order By a.Name, a.Benzos, a.Opioids, a.Medication

Open in new window


OR

;with yourTable as
(
	Select 'Patient A' Name, 'Y' Benzos, 'N' Opioids, 'Xanax' Medication union
	Select 'Patient B' Name, 'Y' Benzos, 'N' Opioids, 'Xanax' Medication union
	Select 'Patient C' Name, 'Y' Benzos, 'N' Opioids, 'Xanax' Medication union
	Select 'Patient C' Name, 'N' Benzos, 'Y' Opioids, 'Lortab' Medication union
	Select 'Patient D' Name, 'N' Benzos, 'Y' Opioids, 'Lortab' Medication union
	Select 'Patient E' Name, 'Y' Benzos, 'N' Opioids, 'Xanax' Medication union
	Select 'Patient E' Name, 'N' Benzos, 'Y' Opioids, 'Hydrocodone' Medication
)
Select a.* from yourTable a
inner join
(
	Select Name
	from
	(
		Select Name from yourTable where Benzos = 'Y'
		union all
		Select Name from yourTable where Opioids = 'Y'
	) a
	Group By Name
	Having Count(Name) = 2
) b on a.Name = b.Name
Order By a.Name, a.Benzos, a.Opioids, a.Medication

Open in new window

PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
>>"patients that take 2 (Or more) ..."

that requires use of (greater than or equal) e.g.       Having Count(Name) >= 2

an alternative, but it lists all details, not just a list of unique names:
select *
from (
   select *
    , count(*) over(partition by Name) as cn
  from yourtable
   ) d
where cn >= 2

Open in new window

awking00Information Technology Specialist

Commented:
select * from yourtable
where name in
(select name from yourtable where Benzos = 'Y'
 intersect
 select name from yourtable where Opioids = 'Y')

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial