Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606

    Unanswered: Removing 'duplicates' in Paired Data

    DBMS: Oracle 8i

    Hi, I have two tables like this:
    Contact( ContacteeID, ContactorID, Type, Details, Date )
    Spouse( PersonID, SpouseID )

    The contact table stores the ID of the person being contacted, the ID of the person that contacted them, the type of the contact, the details of the contact, and the date the contact was performed.
    The spouse table stores the IDs of the spouses. This is table contains data like {(123, 456), (456, 123)} (e.g. one marriage takes two rows).

    We have some reports which gather contact counts over a given time period for some ContactorID. The counts, however, are inflated because we'd like contacts for couples on the same day of the same type to be counted as one.

    Some sample data (I've blanked out non-important data to help improve clarity):
    Code:
    Contact{ (123, --, 'PCR', --, '01/01/2003' ),
             (123, --, 'PCR', --, '01/01/2003' ),
             (123, --, 'AES', --, '01/12/2003' ),
             (222, --, 'ABC', --, '01/23/2003' ),
             (333, --, 'AES', --, '01/12/2003' ),         
             (333, --, 'AES', --, '01/12/2003' ),                  
             (333, --, 'PCR', --, '02/22/2003' ),
             (333, --, 'PCR', --, '02/22/2003' ),
             (333, --, 'ABC', --, '02/22/2003' ),
             (333, --, 'PCR', --, '02/23/2003' ),         
             ... }
             
    Spouse{  (123, 333),
             (333, 123) }
    So if I executed SQL that looked like this:
    Code:
    SELECT COUNT( * ),
           Type
      FROM Contact
     WHERE ContactorID = ...
     GROUP BY Type
    (assuming all the contactors are the same in the above data)

    I'd get something like:
    Code:
    COUNT(*), TYPE
    --------------
    4,        PCR
    3,        AES
    2,        ABC
    What I'd like to see is this:
    Code:
    COUNT(*), TYPE
    --------------
    3,        PCR
    1,        AES
    2,        ABC
    The 3 PCR counts 1 for the 123 user (since it was on the same date), and 2 for the 333 user (1 for the 2/22/03 date and 1 for the 2/23/03 date).
    The 1 AES is because 123 and 333 are married, so we count their AES contacts on the same date as a joint contact of 1.
    ABC is unaffected.

    Any ideas?
    Thanks,

    Matt

  2. #2
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    Also, it ends up being problematic using the Spouse table since there are two rows for each marriage – so if you try to MINUS the spouse IDs from the list it obviously doesn’t work (you end up with no married couples).
    Thanks,

    Matt

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •