Run the complete worked example

Open a new query window connected to your test database. Paste the whole batch below and execute it together. The table variables exist only within the batch. Do not insert GO between the declarations and queries.

The data deliberately includes an old follow-up followed by a resolution, equal contact timestamps, a late-loaded older contact and a customer with no contact. Those are the cases that a clean-looking sample often misses.

-- Illustrative example. Run the whole batch in SQL Server or Azure SQL Database.
-- Table variables only: no permanent business tables are changed.
DECLARE @Customers TABLE (
    CustomerID int PRIMARY KEY,
    CustomerName nvarchar(60) NOT NULL
);
DECLARE @Contacts TABLE (
    ContactID int PRIMARY KEY,
    CustomerID int NOT NULL,
    ContactAt datetime2(0) NOT NULL,
    Outcome nvarchar(30) NOT NULL,
    ContactOwner nvarchar(30) NOT NULL
);
DECLARE @Summary TABLE (
    CustomerID int PRIMARY KEY,
    CustomerName nvarchar(60) NOT NULL,
    ContactID int NULL,
    ContactAt datetime2(0) NULL,
    Outcome nvarchar(30) NULL,
    ContactOwner nvarchar(30) NULL
);

INSERT INTO @Customers VALUES
(101, N'Example Trade'),
(102, N'Example Services'),
(103, N'Example Retail'),
(104, N'Example Logistics');

INSERT INTO @Contacts VALUES
(1, 101, '2026-11-02T09:00:00', N'Follow-up', N'Team A'),
(2, 101, '2026-11-05T11:00:00', N'Resolved',  N'Team B'),
(3, 102, '2026-11-04T10:00:00', N'Follow-up', N'Team A'),
(4, 102, '2026-11-04T10:00:00', N'Resolved',  N'Team C'),
(5, 103, '2026-11-06T14:00:00', N'Follow-up', N'Team B'),
(6, 101, '2026-11-01T08:00:00', N'Follow-up', N'Team C');

;WITH RankedContacts AS (
    SELECT
        ContactID,
        CustomerID,
        ContactAt,
        Outcome,
        ContactOwner,
        ROW_NUMBER() OVER (
            PARTITION BY CustomerID
            ORDER BY ContactAt DESC, ContactID DESC
        ) AS rn
    FROM @Contacts
)
INSERT INTO @Summary (
    CustomerID, CustomerName, ContactID,
    ContactAt, Outcome, ContactOwner
)
SELECT
    c.CustomerID,
    c.CustomerName,
    r.ContactID,
    r.ContactAt,
    r.Outcome,
    r.ContactOwner
FROM @Customers AS c
LEFT JOIN RankedContacts AS r
    ON r.CustomerID = c.CustomerID
   AND r.rn = 1;

-- A: One row for every customer, including those without contacts.
SELECT * FROM @Summary ORDER BY CustomerID;

-- B: Follow-up only when the latest contact says Follow-up.
SELECT * FROM @Summary
WHERE Outcome = N'Follow-up'
ORDER BY CustomerID;

-- C: Coverage checks: expect 4, 4, 3, 1.
SELECT
    (SELECT COUNT(*) FROM @Customers) AS Customers,
    COUNT(*) AS SummaryRows,
    COUNT(ContactID) AS CustomersWithContact,
    SUM(CASE WHEN ContactID IS NULL THEN 1 ELSE 0 END) AS NoContact
FROM @Summary;

-- D: Orphan contacts: expect no rows.
SELECT e.ContactID, e.CustomerID
FROM @Contacts AS e
LEFT JOIN @Customers AS c ON c.CustomerID = e.CustomerID
WHERE c.CustomerID IS NULL;

Read the selection in four parts

1. PARTITION BY CustomerID starts a separate ranking for each customer.
2. ORDER BY ContactAt DESC, ContactID DESC puts the newest timestamp first and settles a tie with the unique ID.
3. r.rn = 1 in the join attaches the chosen contact while preserving customers with no match. Putting this condition in the final WHERE clause would remove those unmatched customers.
4. Filter the stored summary when asking which customers’ latest contact needs follow-up. Do not remove resolved contacts before deciding which record is latest.

The common table expression makes the ranking readable; it is not a saved table or a cache. Its scope is the following statement. Here, the selected rows are inserted into @Summary so subsequent checks use that same result. [2]

Check your latest-record logic

This local checklist sends nothing and stores no responses. It checks implementation evidence, not whether every customer record is accurate.