SQL logic to achieve the below mentioned scenario-CodePudding

I would like to know about the SQL logic to achieve the below scenario.

From the source I need to load the data to target as described below.

Source

ID  Name    Place       Date
1   User 1  Chennai     01-Jun-22
1   User 1  Chennai     02-Jun-22
2   User 2  Bangalore   03-Jun-22
2   User 2  Bangalore   04-Jun-22
1   User 1  Bangalore   05-Jun-22
1   User 1  Bangalore   06-Jun-22
1   User 1  Bangalore   07-Jun-22
1   User 1  Chennai     08-Jun-22

Target

ID  Name    Place       From Date   To Date
1   User 1  Chennai     01-Jun-22   02-Jun-22
2   User 2  Bangalore   03-Jun-22   04-Jun-22
1   User 1  Bangalore   05-Jun-22   07-Jun-22
1   User 1  Chennai     08-Jun-22   08-Jun-22

CodePudding user response：

Solution for your problem:

WITH CT1 AS
(
SELECT ID, Name, Place, "Date",
CASE WHEN CONCAT(ID,Place) != LAG(CONCAT(ID,Place),1,'0') OVER(ORDER BY "Date") THEN 1 ELSE 0END as t
FROM Table1
),
CT2 AS
(
SELECT ID, Name, Place, "Date",
SUM(t) OVER(ORDER BY "Date") as grp
FROM CT1
)
SELECT ID, Name, Place,
MIN("Date") as From_Date,
MAX("Date") as To_Date
FROM CT2
GROUP BY ID, Name, Place,grp
ORDER BY From_Date;

Working Example : db<>fiddle Link

CodePudding user response：

CREATE TABLE #Temp([ID] INT,[Name] VARCHAR(100),[Place] VARCHAR(100),[Date] DATETIME)
INSERT INTO #Temp([ID],[Name],[Place],[Date]) VALUES('1','User1','Chennai','01-06-2022')
INSERT INTO #Temp([ID],[Name],[Place],[Date]) VALUES('1','User1','Chennai','02-06-2022')
INSERT INTO #Temp([ID],[Name],[Place],[Date]) VALUES('2','User2','Bangalore','03-06-2022')
INSERT INTO #Temp([ID],[Name],[Place],[Date]) VALUES('2','User2','Bangalore','04-06-2022')
INSERT INTO #Temp([ID],[Name],[Place],[Date]) VALUES('1','User1','Bangalore','05-06-2022')
INSERT INTO #Temp([ID],[Name],[Place],[Date]) VALUES('1','User1','Bangalore','06-06-2022')
INSERT INTO #Temp([ID],[Name],[Place],[Date]) VALUES('1','User1','Bangalore','07-06-2022')
INSERT INTO #Temp([ID],[Name],[Place],[Date]) VALUES('1','User1','Chennai','08-06-2022')


;WITH A AS(
SELECT
ROW_NUMBER() OVER(ORDER BY [Date]) [Rono],
*,
LEAD([Name]) OVER(ORDER BY [Date]) LeadName,
LEAD([Place]) OVER(ORDER BY [Date]) LeadPlace,
LAG([Name]) OVER(ORDER BY [Date]) LagName,
LAG([Place]) OVER(ORDER BY [Date]) LagPlace,

CASE WHEN LEAD([Name]) OVER(ORDER BY [Date])=[Name] AND LEAD([Place]) OVER(ORDER BY [Date])=[Place] THEN 1 ELSE 0 END F1,
CASE WHEN LAG([Name]) OVER(ORDER BY [Date])=[Name] AND LAG([Place]) OVER(ORDER BY [Date])=[Place] THEN 1 ELSE 0 END F2
FROM #Temp
),
B AS(
SELECT *, 
CASE WHEN (A.F1=1 AND A.F2=0) OR (A.F1=0 AND A.F2=0) THEN LEAD([Rono]) OVER(ORDER BY [Date]) WHEN (A.F1=1 AND A.F2=1) THEN NULL ELSE 0 END [FF]
FROM A 
WHERE A.F1 A.F2!=2
)
SELECT 
B.[ID],B.[Name],B.[Place],
B.[Date] [StrtDate],
ISNULL(AB.[Date],B.[Date]) [EndDate]
FROM B
LEFT JOIN B AB ON B.FF=AB.Rono
WHERE B.FF!=0 OR B.FF IS NULL