search

gaepdit / complaint-tracking

Complaint Tracking System
The Unlicense
1 stars 3 forks source link

Duplicated emails in CTS Prod #190

Closed dougwaldron closed 7 years ago

dougwaldron commented 7 years ago

Can't import duplicates

Imported from JIRA: [CTS-211] Duplicated emails in CTS Prod (original by Douglas Waldron)

dougwaldron commented 7 years ago

OK, I'm not going to import email addresses for disabled accounts (but I'll import them to a hidden column OracleEmail).

After disabling many accounts, this might be less of a problem. If problem remains, may just edit emails by hand...

by 557058:64b0cf35-1c06-4e6d-aac4-6d8e6a3aca41

dougwaldron commented 7 years ago 
SELECT
    EMAIL,
    ACCOUNT_DISABLED,
    count(*)
FROM PRINCIPAL
WHERE EMAIL IN (
    SELECT EMAIL
    FROM PRINCIPAL
    GROUP BY EMAIL
    HAVING count(*) > 1)
       AND ACCOUNT_DISABLED = 'N'
GROUP BY EMAIL, ACCOUNT_DISABLED
HAVING count(*) > 1;

by 557058:64b0cf35-1c06-4e6d-aac4-6d8e6a3aca41

dougwaldron commented 7 years ago

Better query:

SELECT
    upper(EMAIL),
    ACCOUNT_DISABLED,
    count(*)
FROM COMMON.PRINCIPAL
WHERE EMAIL IN (
    SELECT u.EMAIL
    FROM V_USER_ROLE_BRANCH u
INNER JOIN COMMON.PRINCIPAL p
    ON p.OID = u.OID
    WHERE u.USERNAME <> 'UNDEFINED'
    GROUP BY u.EMAIL
    HAVING count(*) > 1)
      AND ACCOUNT_DISABLED = 'N'
GROUP BY EMAIL, ACCOUNT_DISABLED
HAVING count(*) > 1
ORDER BY upper(EMAIL);

Only 21 results

by 557058:64b0cf35-1c06-4e6d-aac4-6d8e6a3aca41

dougwaldron commented 7 years ago

Even better:

SELECT
    upper(EMAIL),
    ACCOUNT_DISABLED,
    count(*)
FROM COMMON.PRINCIPAL
WHERE upper(EMAIL) IN (
    SELECT upper(u.EMAIL)
    FROM V_USER_ROLE_BRANCH u
INNER JOIN COMMON.PRINCIPAL p
    ON p.OID = u.OID
    WHERE u.USERNAME <> 'UNDEFINED'
    GROUP BY upper(u.EMAIL)
    HAVING count(*) > 1
)
      AND ACCOUNT_DISABLED = 'N'
GROUP BY upper(EMAIL), ACCOUNT_DISABLED
HAVING count(*) > 1
ORDER BY upper(EMAIL);

26 results

by 557058:64b0cf35-1c06-4e6d-aac4-6d8e6a3aca41

dougwaldron commented 7 years ago

Doug Waldron mentioned this issue in a commit of ga-epd-it/complaint-tracking:
'Improve handling of emails in data migration'

by 557058:69608212-b764-4434-86ee-a573217c2d6e

dougwaldron commented 7 years ago

Final migration duplicate email list:

✓ ANGEL.WHITE@DNR.STATE.GA.US
BERT.LANGLEY@DNR.STATE.GA.US
HUGH.GALYEAN@DNR.STATE.GA.US
IRENE.BENNETT@DNR.STATE.GA.US
JAMIE.LEWIS@DNR.STATE.GA.US
JANICE.RACHELS@DNR.STATE.GA.US
LISA.MYLER@DNR.STATE.GA.US
MARSHALL.RUFO@DNR.STATE.GA.US
MARZIEH.SHAHBAZAZ@DNR.STATE.GA.US
MAURI.CENTIS@DNR.STATE.GA.US
RUSSELL.NIX @DNR.GA.GOV
SHAHEER.MUHANNA@DNR.STATE.GA.US
TAMRA.FISCHER@DNR.STATE.GA.US
TERESA.SHIFLETT@DNR.STATE.GA.US

by 557058:64b0cf35-1c06-4e6d-aac4-6d8e6a3aca41

dougwaldron commented 7 years ago

Better still:

SELECT
    upper(EMAIL),
    ACCOUNT_DISABLED,
    count(*)
FROM COMMON.PRINCIPAL
WHERE upper(EMAIL) IN (
    SELECT upper(u.EMAIL)
    FROM V_USER_ROLE_BRANCH u
INNER JOIN COMMON.PRINCIPAL p
    ON p.OID = u.OID
    WHERE u.USERNAME <> 'UNDEFINED'
  AND u.ROLE <> 'CTS_MASTER'
    GROUP BY upper(u.EMAIL)
    HAVING count(*) > 1
)
      AND ACCOUNT_DISABLED = 'N'
GROUP BY upper(EMAIL), ACCOUNT_DISABLED
HAVING count(*) > 1
ORDER BY upper(EMAIL);

by 557058:64b0cf35-1c06-4e6d-aac4-6d8e6a3aca41

dougwaldron commented 7 years ago

Final final migration duplicate email list:

✓ ANGEL.WHITE@DNR.STATE.GA.US
✓ BERT.LANGLEY@DNR.STATE.GA.US
✓ HUGH.GALYEAN@DNR.STATE.GA.US
✓ JAMIE.LEWIS@DNR.STATE.GA.US
✓ MARSHALL.RUFO@DNR.STATE.GA.US
✓ MAURI.CENTIS@DNR.STATE.GA.US
✓ RUSSELL.NIX @DNR.GA.GOV
✓ TAMRA.FISCHER@DNR.STATE.GA.US

by 557058:64b0cf35-1c06-4e6d-aac4-6d8e6a3aca41