USE Polaris

-- This script is for reviewing potentially bad postal code entries in patron addresses.
-- It looks for all postal code entries, and shows the details for the most applied entry 
-- on the left (used by most patrons; this could be changed to most applied on addresses),
-- and shows details for other postal code entries on the right


DROP TABLE IF EXISTS #CityCount


CREATE TABLE #CityCount (
    PostalCodeID int,
    PostalCode nvarchar(12),
    City nvarchar(32),
    PatronCount int
)

INSERT INTO #CityCount
SELECT
    pc.PostalCodeID,
    pc.PostalCode,
    pc.City,
    COUNT(DISTINCT pr.PatronID)
FROM Polaris.PostalCodes pc
INNER JOIN Polaris.Addresses a
    on pc.PostalCodeID = a.PostalCodeID
INNER JOIN Polaris.PatronAddresses pa
    on a.AddressID = pa.AddressID
INNER JOIN Polaris.PatronRegistration pr (nolock)
    on pa.PatronID = pr.PatronID
WHERE pc.PostalCode != '99999'
GROUP BY
    pc.PostalCodeID,
    pc.PostalCode,
    pc.City;


WITH MaxPatrons AS (

SELECT
    cc.PostalCode,
    MAX(PatronCount) MaxPatrons
FROM #CityCount cc
GROUP BY
    cc.PostalCode
)

SELECT
    cc.PostalCodeID,
    cc.PostalCode,
    cc.City City_w_MaxPatrons,
    pc.County,
    pc.State,
    pc.CountryID,
    cc.PatronCount,
    cc2.PostalCodeID MatchedPC_PostalCodeID,
    cc2.City MatchedPC_City,
    pc2.County MatchedPC_County,
    pc2.State MatchedPC_State,
    pc2.CountryID MatchedPC_CountryID,
    cc2.PatronCount MatchedPC_PatCounts
FROM #CityCount cc
INNER JOIN MaxPatrons mp
    on cc.PostalCode = mp.PostalCode
        and cc.PatronCount = mp.MaxPatrons
LEFT JOIN Polaris.PostalCodes pc
    on cc.PostalCodeID = pc.PostalCodeID
INNER JOIN #CityCount cc2
    on cc.PostalCode = cc2.PostalCode
        and cc2.PatronCount < mp.MaxPatrons
LEFT JOIN Polaris.PostalCodes pc2
    on cc2.PostalCodeID = pc2.PostalCodeID
GROUP BY
    cc.PostalCodeID,
    cc.PostalCode,
    cc.City,
    pc.County,
    pc.State,
    pc.CountryID,
    cc.PatronCount,
    cc2.PostalCodeID,
    cc2.City,
    pc2.County,
    pc2.State,
    pc2.CountryID,
    cc2.PatronCount
ORDER BY
    cc.PostalCode,
    cc.City,
    cc2.PatronCount DESC,
    cc2.City,
    pc2.County




DROP TABLE IF EXISTS #CityCount