LOs bidrag till SQLUG Challenge 2017

CREATE TABLE #ForceBatch(dummy INT NOT NULL, INDEX idx_cs CLUSTERED COLUMNSTORE);
CREATE TABLE #FirstPurchases
(
    PurchaseID INT NOT NULL
    ,FirstName VARCHAR(50) NOT NULL
    ,ZipCode INT NOT NULL
    ,StreetAddress VARCHAR(50) NOT NULL
    ,LastName VARCHAR(50) NOT NULL
    ,CreditCard CHAR(19) NOT NULL
    ,Email VARCHAR(50) NOT NULL
    ,CustomerID INT NULL
)


INSERT INTO #FirstPurchases WITH (TABLOCK) (PurchaseID, FirstName, ZipCode, StreetAddress, LastName, CreditCard, Email, CustomerID)
    SELECT
        MIN(PurchaseID) AS PurchaseID
        ,Firstname
        ,ZipCode
        ,StreetAddress
        ,LastName
        ,CreditCard
        ,Email
        ,NULL AS CustomerID
     FROM
        dbo.Purchases
        LEFT JOIN #ForceBatch ON 1=2
    GROUP BY
        Firstname
        ,ZipCode
        ,StreetAddress
        ,LastName
        ,CreditCard
        ,Email

DECLARE @Count INT = @@ROWCOUNT

UPDATE
    c
SET
    c.CustomerID = p.PurchaseID
FROM
    (SELECT MIN(PurchaseID) AS PurchaseID FROM #FirstPurchases GROUP BY FirstName) AS n

    INNER JOIN #FirstPurchases AS p
    ON n.PurchaseID = p.PurchaseID

    INNER JOIN #FirstPurchases AS c
    ON  c.PurchaseID >= p.PurchaseID
        AND c.FirstName = p.FirstName
        AND (c.ZipCode = p.ZipCode AND c.StreetAddress = p.StreetAddress AND c.LastName = p.LastName OR c.CreditCard = p.CreditCard OR c.Email = p.Email)

SET @Count = @Count - @@ROWCOUNT
WHILE @Count > 0
BEGIN
    UPDATE
        c
    SET
        c.CustomerID = p.CustomerID
    FROM
        (
            SELECT
                p.PurchaseID
                ,p.Firstname
                ,p.ZipCode
                ,p.StreetAddress
                ,p.LastName
                ,p.CreditCard
                ,p.Email
                ,MIN(CASE WHEN c.CustomerID IS NULL THEN p.PurchaseID ELSE c.CustomerID END) AS CustomerID
            FROM
                (SELECT MIN(PurchaseID) AS PurchaseID FROM #FirstPurchases WHERE CustomerID IS NULL GROUP BY FirstName) AS n

                INNER JOIN #FirstPurchases AS p
                ON n.PurchaseID = p.PurchaseID

                INNER JOIN #FirstPurchases AS c
                ON  c.PurchaseID <= p.PurchaseID
                    AND c.FirstName = p.FirstName
                    AND (c.ZipCode = p.ZipCode AND c.StreetAddress = p.StreetAddress AND c.LastName = p.LastName OR c.CreditCard = p.CreditCard OR c.Email = p.Email)
            GROUP BY
                p.PurchaseID
                ,p.ZipCode
                ,p.CreditCard
                ,p.Firstname
                ,p.StreetAddress
                ,p.LastName
                ,p.Email
        ) AS p

        INNER JOIN #FirstPurchases AS c
        ON  c.PurchaseID >= p.PurchaseID
            AND c.FirstName = p.FirstName
            AND (c.ZipCode = p.ZipCode AND c.StreetAddress = p.StreetAddress AND c.LastName = p.LastName OR c.CreditCard = p.CreditCard OR c.Email = p.Email)
    WHERE
        c.CustomerID IS NULL

    SET @Count = @Count - @@ROWCOUNT
END

INSERT INTO dbo.Customers WITH (TABLOCK) (PurchaseID, CustomerID)
    SELECT
        c.PurchaseID
        ,p.CustomerID
    FROM
        dbo.Purchases AS c
        LEFT JOIN #ForceBatch ON 1=2

        INNER JOIN #FirstPurchases AS p
        ON  c.FirstName = p.FirstName
            AND c.ZipCode = p.ZipCode AND c.CreditCard = p.CreditCard AND c.LastName = p.LastName AND c.StreetAddress = p.StreetAddress AND c.Email = p.Email

DROP TABLE #FirstPurchases
DROP TABLE #ForceBatch