How do I merge similar records with different validity dates?
If this is a table of back-to-back ranges only, your case can be treated as a classic "gaps and islands" problem, where you just need to isolate islands of consecutive ranges and then "condense" them by taking the minimum [from]
and the maximum [to]
per island.
There is an established method of solving this using two ROW_NUMBER calls:
WITH islands AS
(
SELECT
id,
data,
[from],
[to],
island = ROW_NUMBER() OVER (PARTITION BY id ORDER BY [from])
- ROW_NUMBER() OVER (PARTITION BY id, data ORDER BY [from])
FROM
#mergeTest
)
SELECT
id,
data,
[from] = MIN([from]),
[to] = MAX([to])
FROM
islands
GROUP BY
id,
data,
island
;
This query will work in as low version as SQL Server 2005.