I need to retrieve MAX ResponsibleEndDate per Client. In case there is NULL, treat it as MAX date.

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)

Output I need:

My query:

SELECT *,
    MAX(ResponsibleEndDate)OVER(PARTITION BY ClientId) AS MAX_END_DATE    
FROM example
ORDER BY ClientId, ResponsibleStartDate

CodePudding user response：

Aggregate functions, in T-SQL, ignore NULL values and NULL is also treated as having the lowest value, not the highest.

As such you'll need to use a couple of functions to replace the NULL with an arbitrarily high value, and then NULL it again:

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;

CodePudding user response：

Another option is to explicitly check for the existence of 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;

A slight variation is CASE WHEN COUNT(ResponsibleEndDate) = COUNT(*) because COUNT will only count non-null values