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
Our community of experts have been thoroughly vetted for their expertise and industry experience.
The Most Valuable Expert award recognizes technology experts who passionately share their knowledge with the community, demonstrate the core values of this platform, and go the extra mile in all aspects of their contributions. This award is based off of nominations by EE users and experts. Multiple MVEs may be awarded each year.
The Distinguished Expert awards are presented to the top veteran and rookie experts to earn the most points in the top 50 topics.