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?
dBforums
>
Removing 'duplicates' in Paired Data
08-11-03, 12:59
Linear Mode
