This discussion is archived
4 Replies Latest reply: Nov 25, 2012 9:02 PM by Chloe_19 RSS

Matching 2 Tables filter by criteria

Chloe_19 Newbie
Currently Being Moderated
Table 1 contains the IDs and the date they REGISTERED (REGISTER_DTE)
Table 2 contains the he IDs witch details such as when they ENROLED (ENROL_DTE)

What I am trying to do is count the IDs in TABLE 1 which were REGISTERED in SEP/OCT/NOV and have ENROLLED (TABLE2) in the next 3 months from their registration date.

Explain:
ID 1 Registered in SEP and Enrolled in OCT so it fits the criteria
ID 2 Registered in AUG and Enrolled in NOV does NOT FIT (registered AUG)
ID 3 Registered in NOV and has 1 Enrollment and one Drop (count only enrolled IDs) fits criteria
ID 4 Registered in NOV but is not in TABLE 2 since they never Enrolled or Dropped
ID 5 Registered in OCT and Enrolled in MARCH does NOT FIT ( March is out of 3 months enrol range)
ID 6 Registered in SEP and Dropped so does NOT FIT
ID 7 Registered in OCT and Enrolled in DEC so it fits the criteria
ID 8 Registered in OCT and Enrolled in DEC so it fits the criteria
ID 9 Registered in DEC does NOT FIT (only taking SEP/OCT/NOV)

Expected Result:
REGISTERED_MONTH     REGISTER COUNT     ENROLED IN NEXT 3 MONTHS
SEP               2                          1
OCT               3                          2
NOV               2                          1
TABLE 1
CREATE TABLE DAN_REGISTER
(ID    VARCHAR2(12),
REGISTER_DTE   DATE) 

INSERT INTO DAN_REGISTER (ID, REGISTER_DTE) VALUES ('1','05/SEP/11');
INSERT INTO DAN_REGISTER (ID, REGISTER_DTE) VALUES ('2','20/AUG/11');
INSERT INTO DAN_REGISTER (ID, REGISTER_DTE) VALUES ('3','15/NOV/11');
INSERT INTO DAN_REGISTER (ID, REGISTER_DTE) VALUES ('4','20/NOV/11');
INSERT INTO DAN_REGISTER (ID, REGISTER_DTE) VALUES ('5','25/OCT/11');
INSERT INTO DAN_REGISTER (ID, REGISTER_DTE) VALUES ('6','10/SEP/11');
INSERT INTO DAN_REGISTER (ID, REGISTER_DTE) VALUES ('7','03/OCT/11');
INSERT INTO DAN_REGISTER (ID, REGISTER_DTE) VALUES ('8','17/OCT/11');
INSERT INTO DAN_REGISTER (ID, REGISTER_DTE) VALUES ('9','15/DEC/11');
TABLE 2
CREATE TABLE DAN_PRESENCE
(ID    VARCHAR2(12),
STATUS    VARCHAR2(12),
ENROL_DTE   DATE,
SUBJECT    VARCHAR2(12)) 

INSERT INTO DAN_PRESENCE (ID, STATUS,ENROL_DTE,SUBJECT) VALUES ('1','Enrolled','30/OCT/11','PILL');
INSERT INTO DAN_PRESENCE (ID, STATUS,ENROL_DTE,SUBJECT) VALUES ('2','Enrolled','21/NOV/11','CHLL');
INSERT INTO DAN_PRESENCE (ID, STATUS,ENROL_DTE,SUBJECT) VALUES ('3','Dropped','15/JAN/11','KILL');
INSERT INTO DAN_PRESENCE (ID, STATUS,ENROL_DTE,SUBJECT) VALUES ('3','Enrolled','12/FEB/12','SILL');
INSERT INTO DAN_PRESENCE (ID, STATUS,ENROL_DTE,SUBJECT) VALUES ('5','Enrolled','01/MAR/11','MILL');
INSERT INTO DAN_PRESENCE (ID, STATUS,ENROL_DTE,SUBJECT) VALUES ('6','Dropped','11/SEP/11','RILL');
INSERT INTO DAN_PRESENCE (ID, STATUS,ENROL_DTE,SUBJECT) VALUES ('7','Enrolled','01/DEC/11','MILL');
INSERT INTO DAN_PRESENCE (ID, STATUS,ENROL_DTE,SUBJECT) VALUES ('8','Enrolled','15/DEC/11','MILL');
INSERT INTO DAN_PRESENCE (ID, STATUS,ENROL_DTE,SUBJECT) VALUES ('8','Enrolled','15/DEC/11','STILL');
INSERT INTO DAN_PRESENCE (ID, STATUS,ENROL_DTE,SUBJECT) VALUES ('9','Enrolled','01/JAN/11','KILL');
USING: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi

Edited by: Chloe_19 on 25/11/2012 18:12
  • 1. Re: Matching 2 Tables filter by criteria
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    Chloe_19 wrote:
    ... *What I am trying to do is count the IDs in TABLE 1 which were REGISTERED in SEP/OCT/NOV  ...
    Do you mean September-Novemeber 2011, or Spetember-Novemeber regardless of the year?
    and have ENROLLED (TABLE2) in the next 3 months from their registration date.*

    Explain:
    ID 1 Registered in SEP and Enrolled in OCT so it fits the criteria
    ID 2 Registered in AUG and Enrolled in NOV does NOT FIT (registered AUG)
    ID 3 Registered in NOV and has 1 Enrollment and one Drop (count only enrolled IDs) fits criteria ...
    Why does id=3 fit the criteria? If you're only counting rows where dan_presence.status='Enrolled', then the enrollment (on February 12, 2011) was before the registration (Nov. 14, 2011), not in the 3-month window. Is the enrollment date supposed to be February 12, 20<b>12</b>?
    ...TABLE 1
    CREATE TABLE DAN_REGISTER
    (ID    VARCHAR2(12),
    REGISTER_DTE   DATE) 
    
    INSERT INTO DAN_REGISTER (ID, REGISTER_DTE) VALUES ('1','05/SEP/11'); ...
    Thanks for posting the CREATE TABLE and INSERT statements. Remember why you go to all that trouble: so that the people who want to help you can re-create the problem and test their ideas. If you post statements that don't work, it's not all that useful.
    None of these INSERT statements worked on my system.
    Don't try to INSERT a VARCHAR2 (such as '20/AUG/11') into a DATE column. Use TO_DATE to convert a VARCHAR2 to a DATE.
    Here's one way:
    SELECT       TRUNC (r.register_dte, 'MONTH')     AS month
    ,       COUNT (DISTINCT r.id)                AS register_count
    ,       COUNT (DISTINCT p.id)               AS enrolled_in_next_3_months 
    FROM             dan_register     r
    LEFT OUTER JOIN      dan_presence     p  ON     p.id          = r.id
                                AND     p.enrol_dte     BETWEEN     r.register_dte
                                          AND     ADD_MONTHS ( r.register_dte
                                                       , 3
                                                       )
                           AND  p.status     = 'Enrolled'
    WHERE     r.register_dte     >= DATE '2011-09-01'
    AND       r.register_dte     <  DATE '2011-12-01'
    GROUP BY  TRUNC (r.register_dte, 'MONTH')
    ;
  • 2. Re: Matching 2 Tables filter by criteria
    Chloe_19 Newbie
    Currently Being Moderated
    Thanks for the quick reply Frank.

    >
    Do you mean September-Novemeber 2011, or Spetember-Novemeber regardless of the year?
    >
    Yes 2011 so September + 3 months would be OCT/NOV/DEC 2011 and October + 3 months would be NOV/DEC 2011 and JAN 2012
    >
    Why does id=3 fit the criteria? If you're only counting rows where dan_presence.status='Enrolled', then the enrollment (on February 12, 2011) was before the registration (Nov. 14, 2011), not in the 3-month window. Is the enrollment date supposed to be February 12, 2012?
    >
    Yes that is a mistake it is supposed to be 2012
    I ran your querry but I got '0' for the ENROLED IN NEXT 3 MONTHS column
  • 3. Re: Matching 2 Tables filter by criteria
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    Chloe_19 wrote:
    ... I ran your querry but I got '0' for the ENROLED IN NEXT 3 MONTHS column
    Post the exact query you're running, even if (you think) you copied it exactly from my post. I suspect there was an editing error.
    Post some INSERT statements that work on any system, not just yours.
  • 4. Re: Matching 2 Tables filter by criteria
    Chloe_19 Newbie
    Currently Being Moderated
    Thanks Frank

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points