Need to pull customers with specific activity this year (Leads), where that activity (Lead) was their last one

I'm using SSMS to create a report showing customer accounts where the Sales Reps didn't follow up on leads we received this year. That would be indicated in accounts wheriin the list of activities (actions in the account), 'Lead' is the last one listed (the rep didn't take any actions after receiving the lead).

My code is pulling the latest 'Lead' activity for all customers who've had at least one lead this year:

CustomerName	Activity	Date
Bob's Tires	Lead	2021-01-05
Ned's Nails	Lead	2021-02-02
Good Eats	Lead	2021-02-03

I need it to only pull customers where the Lead was the last activity:

CustomerName	Activity	Date
Ned's Nails	Lead	2021-02-02

Here is my code and example tables. What am I missing? I've tried many things with no luck.

WITH activities AS (
    SELECT 
        a. *
        , CASE WHEN a.ContactDate = MAX(CASE WHEN a.Activity LIKE 'Lead%'
               THEN a.ContactDate END) OVER (PARTITION BY a.AcctID)
          THEN 1 ELSE 0 END AS no_followup
    FROM AcctActivities a
    WHERE a.ContactDate >= '2021-01-01'
)
SELECT 
    c.Name,
    act.Activity,
    act.ContactDate
FROM Customers c
INNER JOIN activities act ON c.AcctID = act.AcctID AND act.no_followup = 1
ORDER BY c.AcctID, act.ContactDate ASC

Table 1: Customers (c)

AcctID	CustomerName
11	Bob's Tires
12	Ned's Nails
13	Good Eats
14	Embers

Table 2: Activities (a)

AcctivityID	AcctID	Activity	Date
1	11	Contact Added	2021-01-01
2	11	Lead	2021-01-05
3	11	Phone Call	2021-01-06
4	12	Lead	2021-02-02
5	13	Lead	2021-02-03
6	13	Phone Call	2021-01-15
7	13	Sales Email	2021-01-15
8	14	Cold Call	2021-01-20

Solution

Your approach filters which rows may be considered in the max value in the comparison. I've included the suggested modification below which also modifies your CASE expression to consider that the current row is a lead as the case expression may filter the bounded values to consider (i.e. it will give you the latest lead activity but the latest lead activity may not be your latest activity).

Another modification, possibly optional but safe is adding the ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING in the OVER clause of your partition. While you could have also used UNBOUNDED PRECEDING instead of CURRENT ROW, it seems like extra processing when all the rows before the ordered ContactDate would be already be less than the current value and you are interested in the maximum value for contact date . The window function by default considers all rows current and before. The amendment would ask the window function to look at all the results in the partition after the current row.

Eg.

WITH activities AS 
(
SELECT 
        a. *,
        CASE 
            WHEN a.Activity LIKE 'Lead%' AND
                 a.ContactDate = (MAX(a.ContactDate) OVER (PARTITION BY a.AcctID ORDER BY a.ContactDate ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING  ))
                THEN 1 
                ELSE 0
        END AS no_followup
    FROM 
        AcctActivities a
    WHERE 
        a.ContactDate >= '2021-01-01'

)
SELECT 
    c.Name,
    act.Activity,
    act.ContactDate
FROM 
    Customers c
INNER JOIN 
    activities act ON c.AcctID = act.AcctID
                   AND act.no_followup = 1
ORDER BY 
    c.AcctID, act.ContactDate ASC

Furthermore, if you are only interested in the customer details and all the resulting activity names would be Lead you may consider the following approach which uses aggregation and having clause to filter your desired results. This approach returns less details in the resulting CTE by filtering early.

WITH customers_with_last_activity_as_lead as (
    SELECT AcctID, MAX(ContactDate) as ContactDate
    FROM AcctActivities a
    WHERE a.ContactDate >= '2021-01-01'
    GROUP BY AcctID
    HAVING
        MAX(a.ContactDate) = MAX(
            CASE 
                 WHEN a.Activity LIKE 'Lead%' THEN a.ContactDate
            END
        )
)
SELECT 
    c.Name,
--  'Lead' as Activity, -- Uncomment this line if it is that you would like to see this constant value in your resulting queries.    
    act.ContactDate
FROM 
    Customers c
INNER JOIN 
   customers_with_last_activity_as_lead  act ON c.AcctID = act.AcctID
ORDER BY 
    c.AcctID, act.ContactDate ASC

if all the values aren't a constant/literal Lead then the following approach may assist in retrieving the correct activity name also

WITH customers_with_last_activity_as_lead as (
    SELECT 
        AcctID, 
        REPLACE(MAX(CONCAT(ContactDate,Activity)),MAX(ContactDate),'') as Activity,
        MAX(ContactDate) as ContactDate
    FROM AcctActivities a
    WHERE a.ContactDate >= '2021-01-01'
    GROUP BY AcctID
    HAVING
        MAX(a.ContactDate) = MAX(
            CASE 
                 WHEN a.Activity LIKE 'Lead%' THEN a.ContactDate
            END
        )
)
SELECT 
    c.Name,
    act.Activity, 
    act.ContactDate
FROM 
    Customers c
INNER JOIN 
   customers_with_last_activity_as_lead  act ON c.AcctID = act.AcctID
ORDER BY 
    c.AcctID, act.ContactDate ASC

Let me know if this works for you.