SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table p3 (StudyNum int, SerialNum int, PuffCount int, P1DT DateTime, P2DT DateTime);

-- Two StudyNum/SerialNum groups, each with multiple (two) PuffCount rows
--  and two instance of each of those combinations (8 rows total)
--  One studynum/serialnum group will have all NULLs for P1DT, the other for P2DT
INSERT INTO P3 (StudyNum, SerialNum, PuffCount, P1DT, P2Dt)
VALUES
(1, 1, 1, NULL, '20230328 15:30:21'),
(1, 1, 1, NULL, '20230321 11:30:18'),
(1, 1, 2, NULL, '20230320 18:11:09'),
(1, 1, 2, NULL, '20230331 09:57:41'),
(2, 3, 1, '20230301 12:00:13', NULL),
(2, 3, 1, '20230302 13:11:22', NULL),
(2, 3, 2, '20230315 11:22:45', NULL),
(2, 3, 2, '20230314 16:58:36', NULL)
8 rows affected

SELECT DISTINCT StudyNum, SerialNum, PuffCount
    ,COALESCE(
       First_Value(P1DT) over (PARTITION BY StudyNum, SerialNum ORDER BY P1DT DESC),
       First_Value(P2DT) over (PARTITION BY StudyNum, SerialNum ORDER BY P2DT DESC)
    ) MAXDate
FROM P3


StudyNum
SerialNum
PuffCount
MAXDate


1
1
1
2023-03-31 09:57:41.000

1
1
2
2023-03-31 09:57:41.000

2
3
1
2023-03-15 11:22:45.000

2
3
2
2023-03-15 11:22:45.000


SELECT a.StudyNum, a.SerialNum, a.PuffCount
    , COALESCE(MAX(b.P1DT), MAX(b.P2DT)) MaxDate
FROM P3 a
INNER JOIN P3 b on b.SerialNum = a.SerialNum AND b.StudyNum = a.StudyNum
GROUP BY a.StudyNum, a.SerialNum, a.Puffcount


StudyNum
SerialNum
PuffCount
MaxDate


1
1
1
2023-03-31 09:57:41.000

1
1
2
2023-03-31 09:57:41.000

2
3
1
2023-03-15 11:22:45.000

2
3
2
2023-03-15 11:22:45.000


Warning: Null value is eliminated by an aggregate or other SET operation.

StudyNum	SerialNum	PuffCount	MAXDate
1	1	1	2023-03-31 09:57:41.000
1	1	2	2023-03-31 09:57:41.000
2	3	1	2023-03-15 11:22:45.000
2	3	2	2023-03-15 11:22:45.000