SQL Server 2019

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


(No column name)


Microsoft SQL Server 2019 (RTM-CU16) (KB5011644) - 15.0.4223.1 (X64) 
 Apr 11 2022 16:24:07 
 Copyright (C) 2019 Microsoft Corporation
 Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



CREATE TABLE #source

(

EmployeeName varchar(100),
EffectiveDate date,
CurrentDepartment varchar(100)

);

INSERT INTO #source

VALUES

('Lisa','2017-06-25','Gardening'),
('Lisa','2018-08-17',NULL),
('Lisa','2021-12-05','Catering'),
('Melissa','2015-08-27',NULL),
('Melissa','2017-11-29','Office'),
('Melissa','2020-10-10','Driving'),
('Melissa','2022-07-11',NULL),
('Omar','2019-01-03',NULL),
('Omar','2020-04-07','Retail'),
('Omar','2021-03-29',NULL),
('Pat', '2012-09-12','Laundry'),
('Pat', '2013-10-30',NULL),
('Pat', '2014-11-29',NULL),
('Pat', '2015-08-16',NULL),
('Pat', '2016-11-05',NULL)


CREATE TABLE #destination

(

EmployeeName varchar(100),
EffectiveDate date,
CurrentDepartment varchar(100),
PreviousNonNULLDepartmentIfAvailable varchar(100)

);

INSERT INTO #destination

VALUES

('Lisa','2017-06-25','Gardening',NULL),
('Lisa','2018-08-17',NULL,'Gardening'),
('Lisa','2021-12-05','Catering','Gardening'),
('Melissa','2015-08-27',NULL,NULL),
('Melissa','2017-11-29','Office',NULL),
('Melissa','2020-10-10','Driving','Office'),
('Melissa','2022-07-11',NULL,'Driving'),
('Omar','2019-01-03',NULL,NULL),
('Omar','2020-04-07','Retail',NULL),
('Omar','2021-03-29',NULL,'Retail'),
('Pat', '2012-09-12','Laundry',NULL),
('Pat', '2013-10-30',NULL,'Laundry'),
('Pat', '2014-11-29',NULL,'Laundry'),
('Pat', '2015-08-16',NULL,'Laundry'),
('Pat', '2016-11-05',NULL,'Laundry')
30 rows affected

SELECT *
FROM #source
ORDER BY EmployeeName, EffectiveDate


SELECT *
FROM #destination
ORDER BY EmployeeName, EffectiveDate


EmployeeName
EffectiveDate
CurrentDepartment


Lisa
2017-06-25
Gardening

Lisa
2018-08-17
null

Lisa
2021-12-05
Catering

Melissa
2015-08-27
null

Melissa
2017-11-29
Office

Melissa
2020-10-10
Driving

Melissa
2022-07-11
null

Omar
2019-01-03
null

Omar
2020-04-07
Retail

Omar
2021-03-29
null

Pat
2012-09-12
Laundry

Pat
2013-10-30
null

Pat
2014-11-29
null

Pat
2015-08-16
null

Pat
2016-11-05
null




EmployeeName
EffectiveDate
CurrentDepartment
PreviousNonNULLDepartmentIfAvailable


Lisa
2017-06-25
Gardening
null

Lisa
2018-08-17
null
Gardening

Lisa
2021-12-05
Catering
Gardening

Melissa
2015-08-27
null
null

Melissa
2017-11-29
Office
null

Melissa
2020-10-10
Driving
Office

Melissa
2022-07-11
null
Driving

Omar
2019-01-03
null
null

Omar
2020-04-07
Retail
null

Omar
2021-03-29
null
Retail

Pat
2012-09-12
Laundry
null

Pat
2013-10-30
null
Laundry

Pat
2014-11-29
null
Laundry

Pat
2015-08-16
null
Laundry

Pat
2016-11-05
null
Laundry


/* With Cascading CTE */
WITH CTE AS (SELECT
               EmployeeName, EffectiveDate, CurrentDepartment,
               LAG(CurrentDepartment) OVER(PARTITION BY EmployeeName ORDER BY EffectiveDate) AS Lag_Value,
               ROW_NUMBER() OVER(PARTITION BY EmployeeName ORDER BY EffectiveDate) AS RN,
               CASE WHEN CurrentDepartment IS NOT NULL THEN 1 ELSE 0 END AS Flag 
             FROM #source
), CTE2 AS (SELECT
              EmployeeName, EffectiveDate, CurrentDepartment, RN, Flag, Lag_Value,
              (SELECT TOP(1) CurrentDepartment FROM CTE WHERE Flag = 1 
                 AND RN < a.RN AND EmployeeName = a.EmployeeName) AS PreviousDepartment
               FROM CTE a
)
SELECT EmployeeName, EffectiveDate, CurrentDepartment, 
  CASE 
    WHEN Lag_Value IS NULL THEN PreviousDepartment
    ELSE Lag_Value 
  END AS PreviousNonNULLDepartmentIfAvailableFROM 
FROM CTE2 a


EmployeeName
EffectiveDate
CurrentDepartment
PreviousNonNULLDepartmentIfAvailableFROM


Lisa
2017-06-25
Gardening
null

Lisa
2018-08-17
null
Gardening

Lisa
2021-12-05
Catering
Gardening

Melissa
2015-08-27
null
null

Melissa
2017-11-29
Office
null

Melissa
2020-10-10
Driving
Office

Melissa
2022-07-11
null
Driving

Omar
2019-01-03
null
null

Omar
2020-04-07
Retail
null

Omar
2021-03-29
null
Retail

Pat
2012-09-12
Laundry
null

Pat
2013-10-30
null
Laundry

Pat
2014-11-29
null
Laundry

Pat
2015-08-16
null
Laundry

Pat
2016-11-05
null
Laundry


/* With Subquery */
WITH CTE AS (SELECT
               EmployeeName, EffectiveDate, CurrentDepartment,
               LAG(CurrentDepartment) OVER(PARTITION BY EmployeeName ORDER BY EffectiveDate) AS Lag_Value,
               ROW_NUMBER() OVER(PARTITION BY EmployeeName ORDER BY EffectiveDate) AS RN,
               CASE WHEN CurrentDepartment IS NOT NULL THEN 1 ELSE 0 END AS Flag 
             FROM #source
)
SELECT EmployeeName, EffectiveDate, CurrentDepartment,
  CASE 
    WHEN Lag_Value IS NULL THEN PreviousDepartment
    ELSE Lag_Value 
  END AS PreviousNonNULLDepartmentIfAvailableFROM 
FROM (SELECT *,
  (SELECT TOP(1) CurrentDepartment FROM CTE WHERE Flag = 1 
  AND RN < a.RN AND EmployeeName = a.EmployeeName) AS PreviousDepartment
  FROM CTE a) a


EmployeeName
EffectiveDate
CurrentDepartment
PreviousNonNULLDepartmentIfAvailableFROM


Lisa
2017-06-25
Gardening
null

Lisa
2018-08-17
null
Gardening

Lisa
2021-12-05
Catering
Gardening

Melissa
2015-08-27
null
null

Melissa
2017-11-29
Office
null

Melissa
2020-10-10
Driving
Office

Melissa
2022-07-11
null
Driving

Omar
2019-01-03
null
null

Omar
2020-04-07
Retail
null

Omar
2021-03-29
null
Retail

Pat
2012-09-12
Laundry
null

Pat
2013-10-30
null
Laundry

Pat
2014-11-29
null
Laundry

Pat
2015-08-16
null
Laundry

Pat
2016-11-05
null
Laundry
EmployeeName	EffectiveDate	CurrentDepartment
Lisa	2017-06-25	Gardening
Lisa	2018-08-17	null
Lisa	2021-12-05	Catering
Melissa	2015-08-27	null
Melissa	2017-11-29	Office
Melissa	2020-10-10	Driving
Melissa	2022-07-11	null
Omar	2019-01-03	null
Omar	2020-04-07	Retail
Omar	2021-03-29	null
Pat	2012-09-12	Laundry
Pat	2013-10-30	null
Pat	2014-11-29	null
Pat	2015-08-16	null
Pat	2016-11-05	null