SQLTeam.com | Weblogs | Forums

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 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')

On possibility

use sqlteam
go

if OBJECT_ID('tempdb..#EMP') is not null
	drop TABLE #EMP;
	go


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')

select EMP, DATE_WORKED DateWorkedFrom, LAG(p.DATE_WORKED) OVER (ORDER BY p.DATE_WORKED desc) DateWorkedTo
  From #EMP p

Maybe not the most efficient way, but I did verify that it does require only one actual sort:

INSERT INTO #EMP 
    (EMP, DATE_WORKED, CITY) 
VALUES ('Gopinath','2020-02-03','Mumbai'),('Gopinath','2020-02-06','Bangalore'),
('Gopinath','2020-02-09','Bangalore'), ('Gopinath','2020-02-11','Bangalore'),
('Gopinath','2020-02-14','Uttar Pradesh'),('Gopinath','2020-02-16','Uttar Pradesh'),
('Gopinath','2020-02-20','Mumbai')

;WITH 
cte_prev_cities AS (
    SELECT *, 
        LAG(CITY, 1) OVER(PARTITION BY EMP ORDER BY DATE_WORKED) AS prev_city,
        LAG(DATE_WORKED, 1) OVER(PARTITION BY EMP ORDER BY DATE_WORKED) AS prev_date_worked
    FROM #EMP curr_city
),
cte_next_date_worked AS (
    SELECT *,
        LEAD(DATE_WORKED, 1) OVER(PARTITION BY EMP ORDER BY DATE_WORKED) AS next_date_worked
    FROM cte_prev_cities
    WHERE CITY <> prev_city OR prev_city IS NULL
)
SELECT EMP, 
    DATE_WORKED AS DATE_FROM, 
    ISNULL(DATEADD(DAY, -1, next_date_worked), '20990101') AS DATE_TO,
    CITY AS LOCATION
FROM cte_next_date_worked
ORDER BY EMP, DATE_WORKED