Database

DECLARE	@time_series	XML;
SET @time_series = 
	'<timeSeries>
		<ts><date>2010-06-30</date><value>1.235648</value></ts>
		<ts><date>2010-07-31</date><value>564654.235648</value></ts>
		<ts><date>2010-08-31</date><value>5465465.235648</value></ts>
		<ts><date>2010-09-30</date><value>952031.235648</value></ts>
	</timeSeries>'
;

DECLARE @tbl_time_series	TABLE(
	ts_date			DATETIME
,	ts_value		FLOAT
);

INSERT INTO @tbl_time_series
SELECT		
		TS.item.query('date').value('.', 'datetime')	AS tsdate
	,	TS.item.query('value').value('.', 'float')		AS tsvalue
FROM		
	@time_series.nodes('timeSeries/ts') AS TS(item)
;
	
	SELECT * FROM @tbl_time_series;

WITH t1 AS (
    SELECT  r1.RoadID,r1.BEGIN
    FROM    Roads r1 Left outer join Roads r2
        ON  r1.RoadID = r2.RoadID
        AND r1.BEGIN > r2.BEGIN
        AND r1.BEGIN <= r2.END
    GROUP BY r1.RoadID,r1.BEGIN
    HAVING  Count(r2.BEGIN) = 0
    )
,t2 AS (
    SELECT  r3.RoadID,r3.END
    FROM    Roads r3 Left outer join Roads r4
        ON  r3.RoadID = r4.RoadID
        AND r3.END >= r4.BEGIN
        AND r3.END < r4.END
    GROUP BY r3.RoadID,r3.END
    HAVING  Count(r4.BEGIN) = 0
    )
,ContiguousStretch AS (
    SELECT  t1.RoadID,t1.BEGIN,Min(t2.END) AS END
    FROM    t1 join t2
        ON  t1.RoadID = t2.RoadID
        AND t1.BEGIN <= t2.END
    GROUP BY t1.RoadID,t1.BEGIN
    )
,Gaps AS (
    SELECT  RoadID
    FROM    ContiguousStretch
    GROUP BY RoadID
    HAVING  Count(RoadID) > 1
    )
SELECT  c.RoadID,BEGIN,END
FROM    ContiguousStretch c join gaps g
    ON  c.RoadID = g.RoadID
ORDER BY c.RoadID,BEGIN

WITH ordered AS(
    SELECT  RoadID,BEGIN,END,ROW_NUMBER() OVER(PARTITION BY RoadID ORDER BY BEGIN) as rn
    FROM    roads
    )
SELECT  o1.RoadID,o1.BEGIN,o1.END
FROM    ordered o1,ordered o2
WHERE   o1.RoadID = o2.RoadID
    AND o1.rn = o2.rn -1
    AND o1.END <> o2.BEGIN
UNION
SELECT  o2.RoadID,o2.BEGIN,o2.END
FROM    ordered o1,ordered o2
WHERE   o1.RoadID = o2.RoadID
    AND o1.rn = o2.rn -1
    AND o1.END <> o2.BEGIN

SELECT a.*, b.*
FROM	TableName a,
        TableName b
WHERE a.roadid = b.roadid
AND   b.start = (SELECT MIN(c.start)
                 FROM   TableName c
                 WHERE c.roadid = a.roadid
                 AND   c.Start  > a.start)
AND   a.End <> b.Start
ORDER BY ...

create procedure ptest as
begin
   raiserror( 'message', 10, 10 );
end;
go

sqlcmd -S .\instancename -E -d dbname -i testproc.txt