将NULL视为最大日期

Question

我需要检索每个客户端的MAX ResponsibleEndDate。如果存在NULL，则将其视为MAX日期。

CREATE TABLE example
(Id INT, ClientId INT, ClientName VARCHAR(100), ResponsibleId INT, ResponsibleName VARCHAR(100), ResponsibleStartDate DATE, ResponsibleEndDate DATE);
INSERT INTO example
VALUES
(1, 123, 'John Smith', NULL, NULL, NULL, NULL),
(2, 234, 'Thomas Anderson', 12345, 'Tom Cruise', '2019-04-13', '2020-09-15'),
(3, 234, 'Thomas Anderson', 23456, 'John Travolta', '2020-09-16', '2022-01-15'),
(4, 234, 'Thomas Anderson', 37890, 'Van Damm', '2022-01-16', NULL),
(5, 345, 'Mary Tron', NULL, NULL, NULL, NULL),
(6, 456, 'Jackie Chan', 56789, 'Leo Messi', '2018-05-18', '2022-01-18'),
(7, 567, 'Cristiano Ronaldo', 12345, 'Tom Cruise', '2019-05-28', '2021-08-20'),
(8, 567, 'Cristiano Ronaldo', 37890, 'Van Damm', '2021-07-15', '2022-01-15'),
(9, 567, 'Cristiano Ronaldo', 17956, 'Harry Potter', '2022-01-25', NULL)

我需要的输出：

响应Id>响应结束日期最大日期汤姆·克鲁斯2019-04-132020-09-15约翰·特拉沃尔塔2020-09-16空范达姆2022-01-16空NULL56789利奥·梅西2018年05月18日2022-01-1812345汤姆·克鲁斯2021-08-20空37890范达姆2021-07-152022-01-15179562022-01-25空

Id	ClientId	客户端名称	响应名称	响应开始日期
1	123	John Smith
23	托马斯·安德森	1234	空
3	234	托马斯·安德森	23456	2022-01-15
4	234	托马斯·安德森	37890	空
5	345	Mary Tron	NULL
6	456	成龙
7	567	克里斯蒂亚诺·罗纳尔多	2019-05-28
8	567	克里斯蒂亚诺·罗纳尔多	空
9	567	克里斯蒂亚诺·罗纳尔多	哈利·波特	空

聚合函数，在T-SQL中，忽略NULL值，NULL也被视为具有最低值，而不是最高值。

因此，您需要使用几个函数将NULL替换为任意高的值，然后再次使用NULL：

SELECT ClientID,
ClientName,
{Rest of your columns},
NULLIF(MAX(ISNULL(ResponsibleEndDate,'99991231')) OVER (PARTITION BY ClientId),'99991231') AS MAX_END_DATE    
FROM dbo.example
ORDER BY ClientId,
ResponsibleStartDate;

另一个选项是显式检查NULL的存在

SELECT
ClientID,
ClientName,
{Rest of your columns},
CASE WHEN COUNT(CASE WHEN ResponsibleEndDate IS NULL THEN 1 END) OVER (PARTITION BY ClientId) = 0
THEN MAX(ResponsibleEndDate) OVER (PARTITION BY ClientId)
END AS MAX_END_DATE    
FROM dbo.example
ORDER BY
ClientId,
ResponsibleStartDate;

CASE WHEN COUNT(ResponsibleEndDate) = COUNT(*)略有变化，因为COUNT只计算非零值