How to fetch all the record for every change in values over a period of time from a historical data table using SQL or Spark SQL

Other SQL Server Topics
1

Hi All,
I am new to the forum. I am stuck while fetching data from a historical data from a table. I have tried to explain with an example below. Source table and Expected result is mentioned below. Can anyone help me with the script? Appreciate your help

Source Table Data:
​EmployeeID update_datetime​ ​ Address
​123 ​2014-05-20 10:15:00 ​10 Bourke St
​123 ​ 2014-06-21 11:10:00 ​10 Bourke St
​123 ​2014-10-15 05:05:00 ​15 King st
​123 ​ 2015-10-15 10:15:00 ​30 Queen St
​123 ​ 2017-10-26 08:08:00 ​10 Bourke St
333 ​ 2014-05-20 10:15:00 ​10 Bourke St
333 ​ 2014-06-21 11:10:00 ​15 King St
333 ​ 2014-10-15 05:05:00 ​15 King St
333 ​ 2018-11-18 08:05:01 10 Bourke St

Question: How to fetch all the record for every change in values over a period of time from a historical data table using SQL or Spark SQL
Expected Output should be like below : I need exclude the records which has not changed for 2 consecutive dates but to fetch all the changes over a period of time
​EmployeeID update_datetime​ ​Address
​123 ​ 2014-05-20 10:15:00 ​10 Bourke St
​123 ​ 2014-10-15 05:05:00 ​15 King st
​123 ​ 2015-10-15 10:15:00 ​30 Queen St
​123 ​ 2017-10-26 08:08:00 ​10 Bourke St
333 ​ 2014-05-20 10:15:00 ​10 Bourke St
333 ​2014-06-21 11:10:00 ​15 King St
333 ​ 2018-11-18 08:05:01 10 Bourke St

2 
;WITH cte1 AS
(
	SELECT
		EmployeeId, update_datetime, Address,
		N = ROW_NUMBER() OVER(ORDER BY update_datetime) -
			ROW_NUMBER() OVER(PARTITION BY EmployeeId, Address ORDER BY update_datetime) 
	FROM
		YourTable
),
cte2 AS
(
	SELECT *,
		N2 = ROW_NUMBER() OVER(PARTITION BY N ORDER BY update_datetime)
)
SELECT
	EmployeeId, update_datetime, Address
FROM
	cte2
WHERE
	N2 = 1;
1 Like
3

Thanks a lot James😊I will try this .. I appreciate your quick response