Cacher is the code snippet organizer for pro developers

/*
Title: Find Duplicate Addresses Based on Group Location
Purpose: Condense duplicate address records into single record
Author: Samuel Crisp
Client: Grace Church Kanses -> https://rock.visitgracechurch.com/page/115?serviceJobId=70
Tags: Address, Addresses, Duplicate, Job
---------------------------------------------------------------------------------------------------
Change Log:
  [Date] - [Initials] - [Comment]
  05/5/2020 - SC - Created Script
  10/5/2020 - RJ - Updated with IsMailingLocation being first kept
---------------------------------------------------------------------------------------------------
*/

WITH cte AS (
    SELECT 
        GL.Id as GLID
        , L.Id as LID
        , L.Street1
        , COALESCE(L.Street2,'') as Street2
        , L.City
        , L.State
        , L.PostalCode
        , GL.GroupLocationTypeValueId
        , GL.GroupId
        , GL.IsMailingLocation
        ,ROW_NUMBER() OVER (
            PARTITION BY L.Street1, COALESCE(L.Street2, ''), L.City, L.State, L.PostalCode, GL.GroupId
            ORDER BY GL.GroupId
                    , GL.GroupLocationTypeValueId
                    , L.Street1
                    , L.Street2
                    , L.City
                    , L.State
                    , L.PostalCode
                    , GL.IsMailingLocation DESC ) rownum
    FROM
    [Location] L
INNER JOIN
    GroupLocation GL ON GL.LocationId = L.Id
INNER JOIN
    [Group] G ON GL.GroupId = G.Id
WHERE L.IsActive = 1
AND L.Street1 IS NOT NULL
AND L.PostalCode IS NOT NULL
AND G.GroupTypeId = 10 -- family group
) 

DELETE FROM GroupLocation
WHERE Id IN (
SELECT 
  GLID
FROM 
    cte 
WHERE 
    rownum > 1  )