'connecting records regarding their start and end dates
I have the following records:
+----+------------------+-----------------+-------+
| ID | StartDate | EndDate | Color |
+====+==================+=================+=======+
| 1 | 4/14/22 12:00 AM | 4/14/22 2:45 AM | R |
+----+------------------+-----------------+-------+
| 1 | 4/14/22 3:40 AM | 4/14/22 7:06 AM | R |
+----+------------------+-----------------+-------+
| 1 | 4/14/22 7:06 AM | 4/14/22 7:56 AM | R |
+----+------------------+-----------------+-------+
| 1 | 4/14/22 7:56 AM | 4/14/22 8:00 AM | R |
+----+------------------+-----------------+-------+
| 1 | 4/14/22 7:56 AM | 4/14/22 8:00 AM | R |
+----+------------------+-----------------+-------+
| 2 | 4/14/22 12:00 AM | 4/14/22 4:40 AM | R |
+----+------------------+-----------------+-------+
| 2 | 4/14/22 4:40 AM | 4/14/22 6:00 AM | G |
+----+------------------+-----------------+-------+
| 2 | 4/14/22 6:00 AM | 4/14/22 8:00 AM | G |
+----+------------------+-----------------+-------+
and I want to have an output to: merge rows that are following each other (where prev.EndDate = cur.StartDate) if the colors are same.
the output should look like:
+----+------------------+-----------------+-------+
| ID | StartDate | EndDate | Color |
+====+==================+=================+=======+
| 1 | 4/14/22 12:00 AM | 4/14/22 2:45 AM | R |
+----+------------------+-----------------+-------+
| 1 | 4/14/22 3:40 AM | 4/14/22 8:00 AM | R |
+----+------------------+-----------------+-------+
| 2 | 4/14/22 12:00 AM | 4/14/22 4:40 AM | R |
+----+------------------+-----------------+-------+
| 2 | 4/14/22 4:40 AM | 4/14/22 8:00 AM | G |
+----+------------------+-----------------+-------+
I tried with lead/lag and cte but couldn't manage. any ideas?
sample data:
declare @color table (
ID int,
StartTime datetime,
EndTime datetime,
Color char(1)
)
insert @color values (2707670, '2022-04-14 00:00:00.000','2022-04-14 02:45:00.000', 'R')
insert @color values (2707670, '2022-04-14 03:40:00.000','2022-04-14 07:06:28.977', 'R')
insert @color values (2707670, '2022-04-14 07:06:28.977','2022-04-14 07:56:25.600', 'R')
insert @color values (2707670, '2022-04-14 07:56:25.600','2022-04-14 08:00:00.000', 'R')
thank in advance.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
