Building an Employee Date Matrix

I have a list of employees, along with list of cities they’ve worked in. I need to build a matrix (in SQL Server) of start/end dates by city to determine where they were at any given period in time.

The “end date” would be exactly the date before they appeared at a new location.

I’ve included an example of the source table, and what the output table should appear, along with the code that will build a temp table.

Any recommendations on how I can design this query, and what functions to use?

SOURCE:

EMP     DATE        LOCATION
-----------------------------------
Pinal   2020-01-01  Bangalore
Pinal   2020-01-02  Bangalore
Pinal   2020-01-04  Uttar Pradesh
Pinal   2020-01-06  Uttar Pradesh
Pinal   2020-01-20  Mumbai
Pinal   2020-01-22  Bangalore

Desired query output:

EMP     DATE_FROM   DATE_TO     LOCATION
----------------------------------------------
Pinal   2020-01-01  2020-01-03  Bangalore
Pinal   2020-01-04  2020-01-19  Uttar Pradesh
Pinal   2020-01-20  2020-01-21  Mumbai
Pinal   2020-01-22  2099-01-01  Bangalore


CREATE TABLE #EMP 
(
    EMP VARCHAR(30) NOT NULL  ,
    DATE_WORKED DATE NOT NULL ,
    CITY VARCHAR(30) NOT NULL
);

INSERT INTO #EMP (EMP, DATE_WORKED, CITY) 
VALUES 
('Pinal','2020-01-01','Bangalore'),
('Pinal','2020-01-02','Bangalore'),
('Pinal','2020-01-04','Uttar Pradesh'),
('Pinal','2020-01-06','Uttar Pradesh'),
('Pinal','2020-01-20','Mumbai'),
('Pinal','2020-01-22','Bangalore')

Answer

This is a classic Gaps-and-Islands.

Here we use a CROSS APPLY B to get the Date Range, and then CROSS APPLY C as an ad-hoc tally table

Example

 Select Emp
      ,FromDate = min(D)
      ,ToDate   = max(D)
      ,City
 From (
Select *
      ,Grp = datediff(day,'1900-01-01',d) - row_number() over (partition by Emp,City Order By D)
 From  #Emp A
 Cross Apply (
               Select NextDate = IsNull(min(DateAdd(DAY,-1,Date_Worked)),'2025-01-01')
                From  #Emp 
                Where Emp=A.Emp and Date_Worked>A.Date_Worked
             ) B
 Cross Apply (
               Select Top (DateDiff(DAY,Date_Worked,NextDate)+1) 
                      D=DateAdd(DAY,-1+Row_Number() Over (Order By (Select Null)),Date_Worked) 
                From  master..spt_values n1,master..spt_values n2
             ) C
 --Where EMP ='Victor'
) A
Group By Emp,City,Grp
Order by Emp,FromDate

Returns

Emp     FromDate    ToDate      City
Pinal   2020-01-01  2020-01-03  Bangalore
Pinal   2020-01-04  2020-01-19  Uttar Pradesh
Pinal   2020-01-20  2020-01-21  Mumbai
Pinal   2020-01-22  2025-01-01  Banga
Victor  2020-01-01  2020-01-18  NYC
Victor  2020-01-19  2020-01-24  San Fran
Victor  2020-01-25  2025-01-01  NYC