We help IT Professionals succeed at work.
Get Started

Oracle correlated subquery w/COUNT(DISTINCT) help

Jim Horn
Jim Horn asked
Last Modified: 2015-12-22
Hi All

I have two tables

Name      Columns and data types
t1              groupid varchar2 (16), patientid varchar2 (255), date_range_end date
t2              groupid varchar2 (16), patientid varchar2 (255), evidence_number varchar2 (6), evidence_date date

t1 sample data
H123456, 123456789, 02-SEP-15
H123456, 123456789, 15-OCT-15
H123456, 123456789, 02-DEC-15

t2 sample data
H123456, 123456789, 2, 02-SEP-15
H123456, 123456789, 4, 02-OCT-15
H123456, 123456789, 6, 02-NOV-15
H123456, 123456789, 8, 02-NOV-15

I need to pull off a correlated subquery between these two tables where the return set has COUNT(DISTINCT t2.evidence_number) values WHERE t2.evidence_date <= t1.date_range_end.  So the return set I'm looking for is...

t1.groupid, t1.patientid, t1.date_range_end, the count(DISTINCT evidence_number WHERE t2.evidence_date <= t1.date_range_end)  
H123456, 123456789, 02-SEP-15, 1    --  2 has a date equal to 02-SEP-15
H123456, 123456789, 15-OCT-15, 2   -- 2 and 4 have dates before 15-OCT-15
H123456, 123456789, 02-DEC-15, 4   -- 2, 4, 6, and 8 are all before 02-DEC-15

Thanks in advance.
Watch Question
Most Valuable Expert 2012
Distinguished Expert 2020
This problem has been solved!
Unlock 1 Answer and 7 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE