SQL Server: select MAX Value when LEFT JOIN，然后填充表中的列

Question

我有两个表Response和Distributions:

期望输出的表结构

在这种情况下，有多个响应，但有一个分布。我们只需要在分配日期之前将最新的一个回复与分配联系起来，基本上

MAX(COALESCE(RESPONSE_DATE, CREATED_DATE)) <= ASSIGNMENT_DATE

我尝试的SQL查询:

SELECT 
resp.CONTACT_ID, resp.RESPONSE_ID, resp.RESPONSE_DATE, 
resp.CREATED_DATE, d.ASSIGNMENT_DATE AS DISTRIBUTION_DATE
FROM 
Response resp
LEFT JOIN 
Distribution d ON resp.CONTACT_ID = d.CONTACT_ID
-- 12 Hour Grace Period For assignments created before response
AND DATEADD(hour, -12, COALESCE(resp.RESPONSE_DATE, resp.CREATED_DATE)) <= d.ASSIGNMENT_DATE

查询前两行的DISTRIBUTION_DATE为2020-10-28。

这种情况

DATEADD(hour, -12, COALESCE(resp.RESPONSE_DATE, resp.CREATED_DATE)) <= ASSIGNMENT_DATE

是满足的(见SQL查询返回错误的输出表在附件的截图)。

然而，我希望DISTRIBUTION_DATE为"2020-10-28";仅适用于预期输出中所示的第二行。原因是在分配作业日期之前的最新回复，我不关心最初的X回复(我们应该将一个分布与一个最新回复联系起来)

我试图使用

MAX(DATEADD(hour, -12, COALESCE(resp.RESPONSE_DATE, resp.CREATED_DATE))) <= ASSIGNMENT_DATE 

在JOIN ON条件下，但在SQL中不起作用。

请让我知道如何构建查询并获得预期的输出。

注意:从Response到Distribution的连接必须在CONTACT_ID上，没有显式的JOIN_KEY，它可能导致1:M连接，因为我们有相同的CONTACT_ID(这就是在join on条件下进行大量过滤的原因)，理想的情况是在Distribution表上也有RESPONSE_ID，但这不是数据的结构方式。

TIA

Answer 1

如果我理解您的要求，您将需要运行这样的查询以获得预期的结果:

SELECT CONTACT_ID, RESPONSE_ID, RESPONSE_DATE, CREATED_DATE, CASE WHEN RANKING = 1 THEN DISTRIBUTION_DATE ELSE NULL END AS DISTRIBUTION_DATE
FROM (
SELECT 
resp.CONTACT_ID, resp.RESPONSE_ID, resp.RESPONSE_DATE, 
resp.CREATED_DATE, d.ASSIGNMENT_DATE AS DISTRIBUTION_DATE,
DATEADD(hour, -12, COALESCE(resp.RESPONSE_DATE, resp.CREATED_DATE)) AS GRACE_DATE,
RANK() OVER (
PARTITION BY resp.CONTACT_ID
ORDER BY 
CASE 
WHEN DATEADD(hour, -12, COALESCE(resp.RESPONSE_DATE, resp.CREATED_DATE)) <= d.ASSIGNMENT_DATE
THEN DATEADD(hour, -12, COALESCE(resp.RESPONSE_DATE, resp.CREATED_DATE))
ELSE 
'19000101'
END
DESC ) AS RANKING
FROM 
Response resp
LEFT JOIN 
[Distribution] d ON resp.CONTACT_ID = d.CONTACT_ID
) DerivedTable
ORDER BY CONTACT_ID, RESPONSE_ID

基本上，我使用一个排名来根据分配日期计算最接近的日期，然后知道排名为1的应该是显示分配日期的那个。

Answer 2

我假设响应日期可以为空，这就是您使用COALESCE的原因。

下面的查询将返回日期小于分配日期的最后一个响应。

SELECT  d.*, r.*
FROM    [dbo].[Response] r
INNER JOIN [dbo].[Distribution] d
ON  r.CONTACT_ID = d.CONTACT_ID
WHERE   COALESCE(r.[RESPONSE_DATE],[CREATED_DATE]) =
(
SELECT  MAX(COALESCE(rr.[RESPONSE_DATE],rr.[CREATED_DATE]))
FROM    [dbo].[Response] rr
INNER JOIN [dbo].[Distribution] dd
ON  rr.CONTACT_ID = dd.CONTACT_ID
WHERE   COALESCE(rr.[RESPONSE_DATE],rr.[CREATED_DATE]) < dd.ASSIGNMENT_DATE
AND     rr.CONTACT_ID = r.CONTACT_ID
)

也许可以简化它，但它将返回您需要的内容。此外，如果有两个具有相同日期的响应，则将返回两个响应。