How to select the next lowest value and next highest value from a specific record value that's been flagged?

CREATE TABLE [dbo].[Employee_Salary]
(
     [EmployeeID] [varchar](3) NOT NULL,
     [EffectiveDate] [date] NULL,
     [EndDate] [date] NULL,
     [Salary] [decimal](15, 3) NULL,
     [ContractFlag] [varchar](255) NULL
);

INSERT INTO [dbo].[Employee_Salary] 
VALUES
       ('100','01/01/2022','12/31/2022','10000','Completed'),
       ('100','01/01/2023','12/31/2023','20000','Completed'),
       ('100','01/01/2024','12/31/2024','30000','Active'),
       ('100','01/01/2025','12/31/2025','40000','Pending'),
       ('200','01/01/2022','12/31/2022','25000','Completed'),
       ('200','01/01/2023','12/31/2023','35000','Completed'),
       ('200','01/01/2024','12/31/2024','40000','Active'),
       ('200','01/01/2025','12/31/2025','10000','Pending'),
       ('300','01/01/2022','12/31/2022','20000','Completed'),
       ('300','01/01/2023','12/31/2023','30000','Completed'),
       ('300','01/01/2024','12/31/2024','40000','Active'),
       ('300','01/01/2025','12/31/2025','50000','Pending'),
       ('400','01/01/2024','12/31/2024','60000','Active'),
       ('400','01/01/2025','12/31/2025','10000','Pending')

EmployeeID	EffectiveDate	EndDate	Salary	ContractFlag
100	01/01/2022	12/31/2022	10,000	Completed
100	01/01/2023	12/31/2023	20,000	Completed
100	01/01/2024	12/31/2024	30,000	Active
100	01/01/2025	12/31/2025	40,000	Pending
200	01/01/2022	12/31/2022	25,000	Completed
200	01/01/2023	12/31/2023	35,000	Completed
200	01/01/2024	12/31/2024	35,000	Active
200	01/01/2025	12/31/2025	40,000	Pending
300	01/01/2022	12/31/2022	10,000	Completed
300	01/01/2023	12/31/2023	20,000	Completed
300	01/01/2024	12/31/2024	30,000	Active
300	01/01/2025	12/31/2025	40,000	Pending
400	01/02/2024	12/31/2024	50,000	Active
400	01/01/2025	12/31/2025	60,000	Pending

I need to return:

Next lower value(cannot be equal to current active)

EmployeeID	EffectiveDate	EndDate	Salary	ContractFlag
100	01/01/2023	12/31/2023	20,000	Completed
200	01/01/2023	12/31/2023	25,000	Completed
300	01/01/2023	12/31/2023	20,000	Completed
400	NULL	NULL	NULL	NULL

Next higher value(cannot be equal current active)

EmployeeID	EffectiveDate	EndDate	Salary	ContractFlag
100	01/01/2025	12/31/2025	20,000	Pending
200	01/01/2025	12/31/2025	25,000	Pending
300	01/01/2025	12/31/2025	20,000	Pending
400	01/01/2025	12/31/2025	60,000	Pending

I will insert into another table which should look like this:

EmployeeID	PrevIncreaseDate	NextIncreaseDate	PrevSalary	NextSalary	CurrentSalary
100	01/01/2023	01/01/2025	10,000	40,000	30,000
200	01/01/2022	01/01/2025	25,000	40,000	35,000
300	01/01/2023	01/01/2025	30,000	40,000	30,000
400	NULL	01/01/2025	NULL	60,000	50,000

I've tried using the LAG function, but this isn't effective if there are repeat salary amounts like example for EmployeeID 200. EmployeeID 200 has a current 'Active' Salary of 35,000 with a previous year 'Completed' Salary of also 35,000. The next lowest value is 25,000.

I also tried creating a sub-set of records and selecting the MAX or MIN record

SELECT 
    MAX(Salary), i.EmployeeID, i.EffectiveDate 
FROM 
    [Table] i
WHERE 
    i.Salary < (SELECT MAX(Salary) FROM [Table] f 
                WHERE f.ContractFlag = 'Active')
GROUP BY 
    i.EmployeeID, i.EffectiveDate`

Also note that EmployeeID = 400 does not have a previous salary as they started in 2024.

There may be some cases where an employee does not having a pending contract for next year, and thus, should return null values.

Solution

Here is a solution which meets the needs as stated. The first CTE removes rows where the salary doesn't change. Then its a straight-forward lead and lag as you already know how to do.

with cte1 as (
  select *
    , max(case when ContractFlag = 'Active' then Salary end) over (partition by EmployeeId) ActiveSalary
  from [dbo].[Employee_Salary]
), cte2 as (
  select *
    , lag(EffectiveDate) over (partition by EmployeeId order by EffectiveDate asc) PreviousIncreaseDate
    , lag(Salary) over (partition by EmployeeId order by EffectiveDate asc) PrevSalary
    , lead(EffectiveDate) over (partition by EmployeeId order by EffectiveDate asc) NextIncreaseDate
    , lead(Salary) over (partition by EmployeeId order by EffectiveDate asc) NextSalary
  from cte1
  where ContractFlag = 'Active' or ActiveSalary <> Salary
)
select EmployeeId, PreviousIncreaseDate, NextIncreaseDate, PrevSalary, NextSalary, Salary CurrentSalary
from cte2
where ContractFlag = 'Active'
order by EmployeeId, EffectiveDate asc;

DBFiddle