SELECT
t1.GROUPID
, t2.GROUPID
, t1.DATE_RANGE_END
, COUNT(DISTINCT t2.evidence_number) AS x
FROM t1
INNER JOIN t2 ON t1.GROUPID = t2.GROUPID
AND t1.PATIENTID = t2.PATIENTID
AND t2.evidence_date <= t1.DATE_RANGE_END
GROUP BY
t1.GROUPID
, t2.GROUPID
, t1.DATE_RANGE_END
;
Based on your sample data this is the result of that query:
| GROUPID | GROUPID | DATE_RANGE_END | X |
|---------|---------|-----------------------------|---|
| H123456 | H123456 | December, 02 2015 00:00:00 | 4 |
| H123456 | H123456 | October, 15 2015 00:00:00 | 2 |
| H123456 | H123456 | September, 02 2015 00:00:00 | 1 |
and here is the sample data as inserts
CREATE TABLE T1
(GROUPId varchar2(16), PATIENTID int, DATE_RANGE_END date)
;
INSERT ALL
INTO T1 (GROUPID,PATIENTID,DATE_RANGE_END)
VALUES ('H123456', 123456789, to_date('02-09-2015','dd-mm-yyyy'))
INTO T1 (GROUPID,PATIENTID,DATE_RANGE_END)
VALUES ('H123456', 123456789, to_date('15-10-2015','dd-mm-yyyy'))
INTO T1 (GROUPID,PATIENTID,DATE_RANGE_END)
VALUES ('H123456', 123456789, to_date('02-12-2015','dd-mm-yyyy'))
SELECT * FROM dual
;
CREATE TABLE T2
(GROUPID varchar2(16), PATIENTID int, "EVIDENCE_NUMBER" INT, "EVIDENCE_DATE" date)
;
INSERT ALL
INTO T2 (GROUPID,PATIENTID, EVIDENCE_NUMBER, EVIDENCE_DATE)
VALUES ('H123456', 123456789, 2, to_date('02-09-2015','dd-mm-yyyy'))
INTO T2 (GROUPID,PATIENTID, EVIDENCE_NUMBER, EVIDENCE_DATE)
VALUES ('H123456', 123456789, 4, to_date('02-10-2015','dd-mm-yyyy'))
INTO T2 (GROUPID,PATIENTID, EVIDENCE_NUMBER, EVIDENCE_DATE)
VALUES ('H123456', 123456789, 6, to_date('02-11-2015','dd-mm-yyyy'))
INTO T2 (GROUPID,PATIENTID, EVIDENCE_NUMBER, EVIDENCE_DATE)
VALUES ('H123456', 123456789, 8, to_date('02-11-2015','dd-mm-yyyy'))
SELECT * FROM dual
;
see this as a working demo here: http://sqlfiddle.com/#!4/c1d82/1
/*
drop table t1 purge;
create table t1 (groupid varchar2 (16), patientid varchar2 (255), date_range_end date);
drop table t2 purge;
create table t2(groupid varchar2 (16), patientid varchar2 (255), evidence_number varchar2 (6), evidence_date date);
--t1 sample data
insert into t1 values('H123456', '123456789', to_date('02-SEP-15','DD-MON-YY'));
insert into t1 values('H123456', '123456789', to_date('15-OCT-15','DD-MON-YY'));
insert into t1 values('H123456', '123456789', to_date('02-DEC-15','DD-MON-YY'));
--t2 sample data
insert into t2 values('H123456', '123456789', '2', to_date('02-SEP-15','DD-MON-YY'));
insert into t2 values('H123456', '123456789', '4', to_date('02-OCT-15','DD-MON-YY'));
insert into t2 values('H123456', '123456789', '6', to_date('02-NOV-15','DD-MON-YY'));
insert into t2 values('H123456', '123456789', '8', to_date('02-NOV-15','DD-MON-YY'));
commit;
insert into t1 select dbms_random.string('a',16), '123456789', to_date('02-DEC-15','DD-MON-YY') from dual
connect by level<=50000;
insert into t2 select dbms_random.string('a',16), '123456789', '2', to_date('02-SEP-15','DD-MON-YY') from dual
connect by level<=50000;
*/
set autotrace traceonly
select t1.groupid,
t1.patientid,
t1.date_range_end,
sum(case when t2.evidence_date <= t1.date_range_end then 1 end) total
from t1 join t2 on t1.groupid=t2.groupid and t1.patientid=t2.patientid
group by t1.groupid,
t1.patientid,
t1.date_range_end
order by t1.date_range_end
/
SELECT
t1.GROUPID
, t2.GROUPID
, t1.DATE_RANGE_END
, COUNT(t2.evidence_number) AS x
FROM t1
INNER JOIN t2 ON t1.GROUPID = t2.GROUPID
AND t1.PATIENTID = t2.PATIENTID
AND t2.evidence_date <= t1.DATE_RANGE_END
GROUP BY
t1.GROUPID
, t2.GROUPID
, t1.DATE_RANGE_END
order by t1.date_range_end
;
set autotrace off
select to_char(date_range_end,'mm
select to_char(evidence_date,'mm/
I'll work on the query while I wait.