Queries for some more data issues related to address/phone/emails

BORM / CDM Table data issues

--Active companies with multiple primary Addresses

SELECT A.BO_ID,BC.COMPANYID,COUNT(1) FROM PS_BO_SRCH_ADD A,PS_RD_COMPANY BC,PS_BC B
WHERE A.PRIMARY_IND='Y'
AND A.BO_CM_END_DT>GETDATE()
AND A.CM_USE_END_DT>GETDATE()
AND A.BO_REL_ID = 0
AND A.ROLE_TYPE_ID = 2
AND BC.BO_ID = A.BO_ID AND BC.BO_ID = B.BO_ID AND B.CUST_STATUS='A'
GROUP BY A.BO_ID,BC.COMPANYID
HAVING COUNT(1)>1


--Active companies with multiple primary phones

SELECT A.BO_ID,BC.COMPANYID,COUNT(1) FROM PS_BO_SRCH_PHN A,PS_RD_COMPANY BC,PS_BC B
WHERE A.PRIMARY_IND='Y'
AND A.BO_CM_END_DT>GETDATE()
AND A.CM_USE_END_DT>GETDATE()
AND A.BO_REL_ID = 0
AND BC.BO_ID = A.BO_ID AND BC.BO_ID = B.BO_ID AND B.CUST_STATUS='A'
GROUP BY A.BO_ID,BC.COMPANYID
HAVING COUNT(1)>1


--Active companies with multiple primary Emails

SELECT A.BO_ID,BC.COMPANYID,COUNT(1) FROM PS_BO_SRCH_EML A,PS_RD_COMPANY BC,PS_BC B
WHERE A.PRIMARY_IND='Y'
AND A.BO_CM_END_DT>GETDATE()
AND A.CM_USE_END_DT>GETDATE()
AND A.BO_REL_ID = 0
AND BC.BO_ID = A.BO_ID AND BC.BO_ID = B.BO_ID AND B.CUST_STATUS='A'
GROUP BY A.BO_ID,BC.COMPANYID
HAVING COUNT(1)>1