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, null, '2021-11-26'),
('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
*
,PayNum =
DENSE_RANK() OVER (PARTITION BY [Plan_ID],ee_id,loan_number
ORDER BY COALESCE(payroll_date, process_date) , CASE WHEN payroll_date IS NULL THEN 1 ELSE 0 END)
FROM table1
ORDER BY ISNULL(payroll_date,process_date)
| plan_id | ee_id | loan_number | payroll_date | process_date | PayNum |
|---|---|---|---|---|---|
| aaa | 1234 | 1 | 2021-11-26 | null | 1 |
| aaa | 1234 | 1 | null | 2021-11-26 | 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 |