troubleshooting Question

Oracle correlated subquery w/COUNT(DISTINCT) help

Avatar of Jim Horn
Jim HornFlag for United States of America asked on
Oracle Database
7 Comments1 Solution99 ViewsLast Modified:
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.
Jim
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros