By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table table1 (
plan_id varchar(10),
ee_id integer,
loan_number integer,
payroll_date date,
process_date date);
insert into table1 values
('aaa', 1234, 1, '2021-11-26', null),
('aaa', 1234, 1, '2021-12-23', null),
('aaa', 1234, 1, '2022-02-18', null),
('aaa', 1234, 1, '2022-02-18', null),
('aaa', 1234, 1, null, '2022-02-18');
5 rows affected
select *
from table1
| plan_id | ee_id | loan_number | payroll_date | process_date |
|---|---|---|---|---|
| aaa | 1234 | 1 | 2021-11-26 | null |
| aaa | 1234 | 1 | 2021-12-23 | null |
| aaa | 1234 | 1 | 2022-02-18 | null |
| aaa | 1234 | 1 | 2022-02-18 | null |
| aaa | 1234 | 1 | null | 2022-02-18 |
select plan_id, ee_id, loan_number, payroll_date, process_date,
DENSE_RANK() OVER
(PARTITION BY plan_id,ee_id,loan_number ORDER BY isnull(payroll_date,process_date) asc) as r
from table1
| plan_id | ee_id | loan_number | payroll_date | process_date | r |
|---|---|---|---|---|---|
| aaa | 1234 | 1 | 2021-11-26 | null | 1 |
| aaa | 1234 | 1 | 2021-12-23 | null | 2 |
| aaa | 1234 | 1 | 2022-02-18 | null | 3 |
| aaa | 1234 | 1 | 2022-02-18 | null | 3 |
| aaa | 1234 | 1 | null | 2022-02-18 | 3 |
select plan_id, ee_id, loan_number, payroll_date, process_date,
row_number() OVER
(PARTITION BY plan_id,ee_id,loan_number ORDER BY isnull(payroll_date,process_date) asc) as r
from table1
| plan_id | ee_id | loan_number | payroll_date | process_date | r |
|---|---|---|---|---|---|
| aaa | 1234 | 1 | 2021-11-26 | null | 1 |
| aaa | 1234 | 1 | 2021-12-23 | null | 2 |
| aaa | 1234 | 1 | 2022-02-18 | null | 3 |
| aaa | 1234 | 1 | 2022-02-18 | null | 4 |
| aaa | 1234 | 1 | null | 2022-02-18 | 5 |
select plan_id, ee_id, loan_number, payroll_date, process_date,
dense_RANK() OVER
(PARTITION BY plan_id,ee_id,loan_number ORDER BY case when payroll_date is null then 1 else 0 end, payroll_date,process_date asc) as r
from table1
| plan_id | ee_id | loan_number | payroll_date | process_date | r |
|---|---|---|---|---|---|
| aaa | 1234 | 1 | 2021-11-26 | null | 1 |
| aaa | 1234 | 1 | 2021-12-23 | null | 2 |
| aaa | 1234 | 1 | 2022-02-18 | null | 3 |
| aaa | 1234 | 1 | 2022-02-18 | null | 3 |
| aaa | 1234 | 1 | null | 2022-02-18 | 4 |