Resolve duplicate users

[dougwaldron]

Some users had multiple accounts in previous versions of the CTS. These should be consolidated.

with
    cte (cId)
        as (select CreatedById as cId
            from dbo._archive_Complaints
            union
            select CurrentOwnerId
            from dbo._archive_Complaints
            union
            select DeletedById
            from dbo._archive_Complaints
            union
            select EnteredById
            from dbo._archive_Complaints
            union
            select ReceivedById
            from dbo._archive_Complaints
            union
            select ReviewById
            from dbo._archive_Complaints
            union
            select UpdatedById
            from dbo._archive_Complaints
            union
            select CreatedById
            from dbo._archive_LookupOffices
            union
            select MasterUserId
            from dbo._archive_LookupOffices
            union
            select UpdatedById
            from dbo._archive_LookupOffices
            union
            select CreatedById
            from dbo._archive_ComplaintTransitions
            union
            select TransferredByUserId
            from dbo._archive_ComplaintTransitions
            union
            select TransferredFromUserId
            from dbo._archive_ComplaintTransitions
            union
            select TransferredToUserId
            from dbo._archive_ComplaintTransitions
            union
            select UpdatedById
            from dbo._archive_ComplaintTransitions
            union
            select CreatedById
            from dbo._archive_ComplaintActions
            union
            select DeletedById
            from dbo._archive_ComplaintActions
            union
            select EnteredById
            from dbo._archive_ComplaintActions
            union
            select UpdatedById
            from dbo._archive_ComplaintActions
            union
            select DeletedById
            from dbo._archive_Attachments
            union
            select UploadedById
            from dbo._archive_Attachments
            union
            select CreatedById
            from dbo._archive_LookupActionTypes
            union
            select UpdatedById
            from dbo._archive_LookupActionTypes
            union
            select CreatedById
            from dbo._archive_LookupConcerns
            union
            select UpdatedById
            from dbo._archive_LookupConcerns)
select Id,
       Email
from _archive_AspNetUsers
where replace(replace(UserName, '_', '.'), '@dnr.state.ga.us', '@dnr.ga.gov') in
      (select replace(replace(u.UserName, '_', '.'), '@dnr.state.ga.us', '@dnr.ga.gov') as UserName
       from dbo._archive_AspNetUsers u
       group by replace(replace(u.UserName, '_', '.'), '@dnr.state.ga.us', '@dnr.ga.gov')
       having count(*) > 1)
  and Id in (select cId from cte)
order by NormalizedEmail;