Set previous value as NULL for LEAD function

Question

I have this following query to get the history of location for the employee with EmployeeId = :

SELECT
   e.EmployeeId,
   el.EmployeeLocationId,
   oe.OfficeExtensionId,
   oe.Label,
   [of].OfficeId as 'OfficeId',
   [of].Name as 'Office Name',
   el.LocationDate AS LocationStartDate,
   LEAD(el.LocationDate) OVER ( 
ORDER BY
   el.LocationDate) AS LocationEndDate 
FROM
   Employee_Location el 
   LEFT JOIN
      Employee e 
      ON el.EmployeeId = e.EmployeeId 
   LEFT JOIN
      OfficeExtension oe 
      ON el.OfficeExtensionId = oe.OfficeExtensionId 
   INNER JOIN
      Office [of] 
      ON oe.OfficeId = [of].OfficeId 
WHERE
   el.EmployeeId = 5625

This is my output :

EmployeeId  EmployeeLocationId  OfficeExtensionId   OfficeExtensionId   Label      LocationDateStart           LocationDateEnd
5625        6265                156                 156                 Romania    NULL                        2019-04-09 08:15:37.9583552
5625        6265                156                 156                 Romania    2019-04-09 08:15:37.9583552 2019-07-02 11:00:24.4420116
5625        6390                83                  83                  Spain      2019-07-02 11:00:24.4420116 NULL

I want to set NULL value to LocationDateEnd where there is no LocationDateStart, so my expected output should be like below :

EmployeeId  EmployeeLocationId  OfficeExtensionId   OfficeExtensionId   Label      LocationDateStart           LocationDateEnd
5625        6265                156                 156                 Italy      NULL                        NULL
5625        6265                156                 156                 Romania    2019-04-09 08:15:37.9583552 2019-07-02 11:00:24.4420116
5625        6390                83                  83                  Spain      2019-07-02 11:00:24.4420116 NULL

Answer 1

Just wrap the LEAD() function inside a CASE expression:

el.LocationDate AS LocationStartDate,
CASE WHEN el.LocationDate IS NOT NULL THEN LEAD(el.LocationDate)
                                           OVER (ORDER BY el.LocationDate)
END AS LocationEndDate,