--- In firebird-support@yahoogroups.com, "ehaerim" wrote:

> I executed the answer and got the following results with no
> contiguous ranges at all:

You're right, I did two mistakes. The first one was just not noticing that it was a TIME column and not a TIMESTAMP column, the second was a logical error in assuming that the first and last entries of the contiguous range differed by less than 1. To fix this, I actually had to do a double NOT EXISTS, complicating the whole SQL a bit further (so a stored procedure or execute block will probably look simpler):

SELECT cast('Empty' as CHAR(10)) as MyRange, t1.T as MinValue, t2.T as MaxValue
FROM T_10199000 t1
JOIN T_10199000 t2
on t1.D = t2.D
and t1.P = t2.P
and t1.V = t2.V
and t1.T < t2.T
LEFT JOIN T_10199000 tNot
on t1.D = tNot.D
and t1.P = tNot.P
and t1.V = tNot.V
and t1.T < tNot.T
and t2.T > tNot.T
where tNot.D is NULL
and t2.T - t1.T > 1
union
SELECT 'Contiguous', t1.T, t2.T
FROM T_10199000 t1
JOIN T_10199000 t2
on t1.D = t2.D
and t1.P = t2.P
and t1.V = t2.V
and t1.T < t2.T
LEFT JOIN T_10199000 tNot1
on t1.D = tNot1.D
and t1.P = tNot1.P
and t1.V = tNot1.V
and t1.T > tNot1.T
and t1.T between tNot1.T and tNot1.T + 1 /*Just before*/
LEFT JOIN T_10199000 tNot2
on t2.D = tNot2.D
and t2.P = tNot2.P
and t2.V = tNot2.V
and t2.T < tNot2.T
and t2.T between tNot2.T - 1 and tNot2.T /*Just after*/
where tNot1.D is NULL /*None just before*/
and tNot2.D is NULL /*None just after*/
and not exists(select * FROM T_10199000 t3
where t1.D = t3.D
and t3.T between T1.T and T2.T
and t3.T < T2.T
and not exists(select * FROM T_10199000 t4
where t1.D = t4.D
and t4.T between t3.T and t3.T + 1
and t4.T > t3.T))

This should work, I actually created the tables and tried it this time ;o)

HTH,
Set