Employee Location History Table

Transact-SQL
1

I have this employee location history table.
I need to transform/collapse it, to have a unique FROM_DATE and To_Date - Example below.
How can I achieve this in T-SQL? Thanks!

SOURCE TABLE:

EMP CITY DATE
7164 MUMBAI 1/1/2017
7164 MUMBAI 1/3/2017
7164 PUNE 1/5/2017
7164 PUNE 1/9/2017
7164 BANGALORE 1/10/2017
7164 BANGALORE 1/13/2017

OUTPUT:

EMP CITY FROM_DATE TO_DATE
7164 MUMBAI 1/1/2017 1/3/2017
7164 PUNE 1/5/2017 1/9/2017
7164 BANGALORE 1/10/2017 1/13/2017

2 
DECLARE @tv_Source AS TABLE
( Emp INT NOT NULL,
  City VARCHAR(50) NOT NULL,
  Date DATE NOT NULL
)

INSERT INTO @tv_Source(Emp,City,Date)
SELECT 7164,'MUMBAI',	'1/1/2017' UNION ALL
SELECT 7164,'MUMBAI','1/3/2017' UNION ALL
SELECT 7164,'PUNE','1/5/2017' UNION ALL
SELECT 7164,'PUNE','1/9/2017' UNION ALL
SELECT 7164,'BANGALORE','1/10/2017' UNION ALL
SELECT 7164,'BANGALORE','1/13/2017'
        
 
SELECT
    Emp,
    City,    
    MIN(Date) AS From_Date,
    MAX(Date) AS To_Date
FROM
    @tv_Source AS S
GROUP BY
    Emp,
    City
ORDER BY 
    From_Date


Emp		City		From_Date	To_Date
7164		MUMBAI		01.01.2017	03.01.2017
7164		PUNE		05.01.2017	09.01.2017
7164		BANGALORE	10.01.2017	13.01.2017

http://rextester.com/SARJH90931

3

thanks so much, however employees travel back to previous cities.
I need a unique grouping by date, by location. For example ...

Jan 1 Mumbai
Jan 2 Bangalore
Jan 3 Mumbai

If you use the example above by using the MIN and MAX will create Overlap ... The output will return this:

Mumbai - Jan 1:Jan 3
Bangalore - Jan 2

How can this be written without any overlap?

Thanks!

4

Try this:

select emp
      ,city
      ,min([date]) as from_date
      ,max([date]) as to_date
  from (select emp
              ,city
              ,[date]
              ,rn
	          -row_number() over(partition by emp
                                             ,city
                                 order by rn
                                )
               as rn
          from (select emp
                      ,city
                      ,[date]
                      ,row_number() over(partition by emp
                                         order by [date]
                                                 ,city
                                        )
                       as rn
                  from yourtable
               ) as a
       ) as a
 group by emp
         ,city
         ,rn
 order by emp
         ,from_date
;
1 Like