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?
Thanks!
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
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')