By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE mytable(
Employee VARCHAR(4) NOT NULL
,Leave_Date DATE NOT NULL
);
INSERT INTO mytable(Employee,Leave_Date) VALUES ('John','2020-01-25');
INSERT INTO mytable(Employee,Leave_Date) VALUES ('John','2020-01-26');
INSERT INTO mytable(Employee,Leave_Date) VALUES ('John','2020-01-27');
INSERT INTO mytable(Employee,Leave_Date) VALUES ('John','2020-01-28');
INSERT INTO mytable(Employee,Leave_Date) VALUES ('John','2020-03-15');
INSERT INTO mytable(Employee,Leave_Date) VALUES ('John','2020-03-16');
INSERT INTO mytable(Employee,Leave_Date) VALUES ('Mary','2020-02-12');
INSERT INTO mytable(Employee,Leave_Date) VALUES ('Mary','2020-02-13');
INSERT INTO mytable(Employee,Leave_Date) VALUES ('Mary','2020-02-20');
9 rows affected
select t.*,
row_number() over(
partition by employee, dateadd(day, -rn, leave_date)
order by leave_date
) counter
from (
select t.*,
row_number() over(partition by employee order by leave_date) rn
from mytable t
) t
order by employee, leave_date
| Employee | Leave_Date | rn | counter |
|---|---|---|---|
| John | 2020-01-25 | 1 | 1 |
| John | 2020-01-26 | 2 | 2 |
| John | 2020-01-27 | 3 | 3 |
| John | 2020-01-28 | 4 | 4 |
| John | 2020-03-15 | 5 | 1 |
| John | 2020-03-16 | 6 | 2 |
| Mary | 2020-02-12 | 1 | 1 |
| Mary | 2020-02-13 | 2 | 2 |
| Mary | 2020-02-20 | 3 | 1 |