I have a list of employees, along with list of Cities they've worked in. I need to build a matrix (in MS SQL) 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?
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
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')