SQL Server 2019

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

CREATE TABLE t
    ([YEAR] int, [REFERENCE] varchar(15), [HEADER] varchar(10), [USER] varchar(7), [DATE] varchar(10), [ID] varchar(12), [Key] varchar(8))
;
    
INSERT INTO t
    ([YEAR], [REFERENCE], [HEADER], [USER], [DATE], [ID], [Key])
VALUES
    (2019, 'Charity Funding', NULL, 'UCRP511', '2020-01-15', '0128923961US', NULL),
    (2019, 'Charity Funding', NULL, 'UCRP511', '2020-01-15', '0128923961US', 'JBOT_P10'),
    (2019, 'Charity Funding', NULL, 'UCRP511', '2020-01-15', '0128923961US', NULL),
    (2019, 'Charity Funding', NULL, 'UCRP511', '2020-01-15', '0128923961US', NULL),
    (2019, 'Charity Funding', NULL, 'UCRP511', '2020-01-15', '0128923961US', NULL),
    (2019, 'SJ/1019/233', NULL, 'UCRP511', '2020-01-10', NULL, NULL),
    (2019, 'Computer Comp', NULL, 'UCRP511', '2020-01-15', '024543342OS1', NULL),
    (2019, 'SJ/1019/231', NULL, 'UCRP539', '2020-01-10', NULL, NULL),
    (2019, 'PAYROLL (SC)', 'LABOR DIST', 'UCRP539', '2020-01-10', NULL, 'JBOT_P10')
;
9 rows affected

select * from t


YEAR
REFERENCE
HEADER
USER
DATE
ID
Key


2019
Charity Funding
null
UCRP511
2020-01-15
0128923961US
null

2019
Charity Funding
null
UCRP511
2020-01-15
0128923961US
JBOT_P10

2019
Charity Funding
null
UCRP511
2020-01-15
0128923961US
null

2019
Charity Funding
null
UCRP511
2020-01-15
0128923961US
null

2019
Charity Funding
null
UCRP511
2020-01-15
0128923961US
null

2019
SJ/1019/233
null
UCRP511
2020-01-10
null
null

2019
Computer Comp
null
UCRP511
2020-01-15
024543342OS1
null

2019
SJ/1019/231
null
UCRP539
2020-01-10
null
null

2019
PAYROLL (SC)
LABOR DIST
UCRP539
2020-01-10
null
JBOT_P10


select t.*
from t
where (t.id is null and t.[key] is null) or
      (not exists (select 1
                  from t t2
                  where t2.id = t.id and t2.[key] is not null
                 ) and
       t.[key] is null
      )


YEAR
REFERENCE
HEADER
USER
DATE
ID
Key


2019
SJ/1019/233
null
UCRP511
2020-01-10
null
null

2019
Computer Comp
null
UCRP511
2020-01-15
024543342OS1
null

2019
SJ/1019/231
null
UCRP539
2020-01-10
null
null


select t.*
from (select t.*,
             max([key]) over (partition by id) as max_key
      from t
     ) t
where max_key is null or (id is null and [key] is null)


YEAR
REFERENCE
HEADER
USER
DATE
ID
Key
max_key


2019
SJ/1019/233
null
UCRP511
2020-01-10
null
null
JBOT_P10

2019
SJ/1019/231
null
UCRP539
2020-01-10
null
null
JBOT_P10

2019
Computer Comp
null
UCRP511
2020-01-15
024543342OS1
null
null


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

YEAR	REFERENCE	HEADER	USER	DATE	ID	Key
2019	Charity Funding	null	UCRP511	2020-01-15	0128923961US	null
2019	Charity Funding	null	UCRP511	2020-01-15	0128923961US	JBOT_P10
2019	Charity Funding	null	UCRP511	2020-01-15	0128923961US	null
2019	Charity Funding	null	UCRP511	2020-01-15	0128923961US	null
2019	Charity Funding	null	UCRP511	2020-01-15	0128923961US	null
2019	SJ/1019/233	null	UCRP511	2020-01-10	null	null
2019	Computer Comp	null	UCRP511	2020-01-15	024543342OS1	null
2019	SJ/1019/231	null	UCRP539	2020-01-10	null	null
2019	PAYROLL (SC)	LABOR DIST	UCRP539	2020-01-10	null	JBOT_P10