需要吸引今年有特定活动(Lead)的客户，而该活动(Lead)是他们的最后一次活动

Question

我正在使用SSMS创建一个报告，显示销售代表今年没有跟进我们收到的潜在客户的客户账户。这将在活动列表(帐户中的操作)中的帐户中显示，"Lead"是列出的最后一个(代表在收到Lead后没有采取任何操作)。

我的代码正在为今年至少有一个潜在客户的所有客户提取最新的"Lead"活动:

活动tbody> <<tr>Ned的指甲

CustomerName	日期
鲍勃的轮胎	铅	2021-01-05
	铅	2021-02-02
好吃	铅	2021-02-03

您的方法可以筛选哪些行可能在比较中的最大值中被考虑。我在下面包含了建议的修改，它也修改了您的CASE表达式，以考虑当前行是一个引线，因为CASE表达式可能会过滤要考虑的有界值(即它会给您最新的引线活动，但最新的引线活动可能不是您的最新活动)。

另一个修改，可能是可选的，但安全的是在分区的OVER子句中添加ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING。虽然您也可以使用UNBOUNDED PRECEDING而不是CURRENT ROW，但当有序的ContactDate之前的所有行已经小于当前值并且您对联系日期的最大值感兴趣时，似乎需要额外的处理。默认情况下，窗口函数考虑当前和以前的所有行。该修改将要求窗口函数查看当前行之后分区中的所有结果。

。

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

此外，如果您只对客户详细信息感兴趣，并且所有结果活动名称都是Lead，您可以考虑以下方法，该方法使用聚合和having子句来过滤您想要的结果。这种方法通过早期过滤，在生成的CTE中返回较少的细节。

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

如果所有的值都不是常量/文字Lead，那么下面的方法也可以帮助检索正确的活动名称

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

让我知道这是否适合你。

我同意Gordon的观点，你的问题不是很清楚(你真的只关心今年的活动吗?)no_followup的本意是什么?话虽如此，我认为这是你想要的:

select      c.Name, 
lastActivity.Activity, 
lastActivity.ContactDate
from        Customers c
cross apply (
select   top 1 a.Activity, a.ContactDate
from     activities a 
where    a.acctId = c.acctId 
-- and a.ContactDate >= '2021-01-01' uncomment this if you only care about activity this year
order by a.ContactDate desc
) lastActivity
where       lastActivity.Activity like 'Lead%'