Thursday 17 July 2014

List of Phones used by more than one accounts

/* Formatted on 7/17/2014 12:09:28 PM (QP5 v5.115.810.9015) */
SELECT hcp_out.phone_area_code,
       hcp_out.phone_number,
       hca_out.account_number,
       party_name
FROM hz_contact_points hcp_out,
     hz_cust_accounts hca_out,
     hz_parties party,
     (SELECT hcp.phone_area_code, hcp.phone_number
      FROM hz_contact_points hcp,
           hz_cust_accounts hca,
           (SELECT hcp_in.phone_area_code, hcp_in.phone_number
            FROM hz_contact_points hcp_in
            WHERE hcp_in.creation_date >= TO_DATE ('1-JAN-2008', 'DD-MON-YYYY')
                  AND hcp_in.creation_date <
                        TO_DATE ('1-JAN-2012', 'DD-MON-YYYY')
                  AND hcp_in.owner_table_name = 'HZ_PARTIES'
                  AND hcp_in.contact_point_type = 'PHONE') new_phone
      WHERE     hcp.phone_area_code = new_phone.phone_area_code
            AND hcp.phone_number = new_phone.phone_number
            AND hca.party_id = hcp.owner_table_id
      GROUP BY hcp.phone_area_code, hcp.phone_number
      HAVING COUNT (DISTINCT hca.account_number) > 1) dup_phone
WHERE     hcp_out.phone_area_code = dup_phone.phone_area_code
      AND hcp_out.phone_number = dup_phone.phone_number
      AND hca_out.party_id = hcp_out.owner_table_id
      AND party.party_id = hca_out.party_id
ORDER BY hcp_out.phone_area_code,
         hcp_out.phone_number,
         hca_out.account_number

No comments:

Post a Comment