SQL查找条件不存在的最新日期

Question

我对 SQL 还很陌生，并且正在处理一个已经彻底击败我一段时间的查询。我经常访问此站点 - 由于您的所有专业知识，这是一个了不起的资源，通常我会找到我需要的东西，但这次我认为我的查询有点太具体了，我没有找到适用的东西。有人可以帮帮我吗？

我有两张表:一张客户表和一张联系人(又名约会)表。我需要找到所有客户最近的约会日期(在特定日期之前，在本例中为“11/08/2015”)，当天任何约会的结果都不是“2”。每个客户在一天内可能有多个约会，并且任何这些约会的结果为“2”意味着我们必须忽略一整天并返回到下一个最近的一天。

例如，客户“3”的返回预约日期应该是“01/07/2015”(只有一行)，而不是“16/07/2015”的两行，因为其中一个预约日期“16/07/2015”的结果为“2”。 Outcome 的所有其他值都是可接受的(包括 NULL)，只是不是“2”。

同一天的多次约会是我觉得棘手的部分 - 我可以使用 Select MAX(或 TOP 1)语句找到最新的约会日期，但是当我添加“<> '2 '"它仍然继续返回可能有结果 '2' 的同一天，因为同一天的其他约会有另一个结果。我一直在尝试使用我的表格和 GROUP BY 和 NOT EXIST，但我似乎没有取得任何进展。

Contact
ClientID    AppDate Outcome
1   30/07/2015 17:00    2
1   01/07/2015 17:00    3
2   03/03/2015 16:00    NULL
2   01/03/2015 16:00    NULL
3   16/07/2015 15:40    6
3   16/07/2015 15:40    2
3   01/07/2015 15:40    3
4   05/08/2015 12:30    6
4   05/08/2015 12:30    2
4   01/08/2015 12:30    3
5   23/07/2015 15:30    2
5   23/07/2015 15:30    NULL
5   01/07/2015 15:30    4
6   20/07/2015 10:10    NULL
6   20/07/2015 10:10    2
6   01/07/2015 10:10    6
7   23/07/2015 15:40    2
7   01/07/2015 15:40    1
7   23/06/2015 15:40    8
8   13/07/2015 11:30    2
8   13/07/2015 11:30    6
8   01/07/2015 11:30    2
8   01/06/2015 11:30    3
9   29/07/2015 17:00    3
9   29/07/2015 17:00    6
10  14/07/2015 11:00    NULL
10  01/07/2015 11:00    5


Client      
ClientID    Forename    Surname
1       I       B
2       J       B
3       S       C
4       S       T
5       P       C
6       K       D
7       P       E
8       P       H
9       S       F
10      A       G

如果我遗漏了一些明显的东西，我深表歉意!感谢阅读和任何回复。我附上我的截断查询供您一般娱乐...

SELECT
cli.ClientID ,
cli.Forename ,
cli.Surname ,
con.AppDate ,
con.Outcome 


FROM
Client AS cli

INNER JOIN
Contact AS con
ON cli.ClientID = con.ClientID 
AND con.AppDate =
    (SELECT MAX(con1.AppDate) 
    FROM Contact AS con1
    WHERE con.ClientID = con1.ClientID 
    AND con1.AppDate < '11/08/2015  00:00:00'
    AND con1.Outcome <> '2')


ORDER BY
cli.ClientID 

编辑:

感谢 Linoff 先生提供的交叉应用查询，它运行良好。

抱歉，我之前没有包含预期的输出。供引用(供将来处理类似问题的其他人使用)我希望获得:

Appointments        
Client ID   Act Date and Time   Outcome
1   01/07/2015 17:00    3
2   03/03/2015 16:00    NULL
3   01/07/2015 15:40    3
4   01/08/2015 12:30    3
5   01/07/2015 15:30    4
6   01/07/2015 10:10    6
7   01/07/2015 15:40    1
8   01/06/2015 11:30    3
9   29/07/2015 17:00    3
9   29/07/2015 17:00    6
10  14/07/2015 11:00    NULL

Answer 1

我认为交叉应用是最好的方法:

select c.*, con.*
from client c cross apply
     (select top 1 con.*
      from (select con.*,
                   sum(case when Outcome = 2 then 1 else 0 end) over (partition by ClientId, AppDate) as num2s
            from contact con
            where con.ClientId = c.ClientId and
                  con.AppDate < '2015-11-08'
           ) con
      where num2s = 0
      order by AppDate desc
     ) con;

在这种情况下，cross apply 的工作方式很像相关子查询，但您可以返回多个值。子查询使用窗口函数来计算给定日期“2”的数量，其余逻辑应该非常明显。

这会返回最近日期的一行，其中包含适当的约会。如果您想要多个这样的行，请使用 with ties。