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

MYRANGE MINVALUE MAXVALUE
========== ============= =============
Empty 10:11:11.0000 10:11:12.0000
Empty 10:11:12.0000 10:11:12.0001
Empty 10:11:12.0001 10:11:15.0000
Empty 10:11:15.0000 10:11:15.0001
Empty 10:11:15.0001 10:11:18.0000
Empty 10:11:18.0000 10:11:18.0001

But what I expected is one of the following 3 results:

[1] returning both contiguous and empty ranges
MYRANGE MINVALUE MAXVALUE
========== ============= =============
Contiguous 10:11:11 10:11:12
Empty 10:11:13 10:11:14
Contiguous 10:11:15 10:11:15
Empty 10:11:16 10:11:17
Contiguous 10:11:18 10:11:18

[2] returning emtpy ranges only
MYRANGE MINVALUE MAXVALUE
========== ============= =============
Empty 10:11:13 10:11:14
Empty 10:11:16 10:11:17

[3] returning contiguous ranges only
MYRANGE MINVALUE MAXVALUE
========== ============= =============
Contiguous 10:11:11 10:11:12
Contiguous 10:11:15 10:11:15
Contiguous 10:11:18 10:11:18

I think the code needs a little fix for this.
thx
HR

--- In firebird-support@yahoogroups.com, "ehaerim" <ehaerim@...> wrote:
>
> A table is created using
>
> CREATE TABLE T_10199000(D DATE NOT NULL, T TIME NOT NULL, P DOUBLE PRECISION NOT NULL, V FLOAT NOT NULL, PRIMARY KEY (D, T), CHECK(V >= 0));
>
> Given a search range, (FromDate, FromTime) and (ToDate, ToTime),
>
> [1] I would like to find out all the ranges each of which is either contiguous or empty. Here "contiguous" means the seconds values are in a row in ascending order without any gap. "Empty" means literally a vacant range without any data.
>
> (ex)
> Following 7 records were inserted into the table.
> INSERT INTO T_10199000(D, T, P, V) VALUES('2011-05-13', '10:11:11.0000', 280.85, 25.0);
> INSERT INTO T_10199000(D, T, P, V) VALUES('2011-05-13', '10:11:12.0000', 280.85, 25.0);
> INSERT INTO T_10199000(D, T, P, V) VALUES('2011-05-13', '10:11:12.0001', 280.85, 25.0);
> INSERT INTO T_10199000(D, T, P, V) VALUES('2011-05-13', '10:11:15.0000', 280.85, 25.0);
> INSERT INTO T_10199000(D, T, P, V) VALUES('2011-05-13', '10:11:15.0001', 280.85, 25.0);
> INSERT INTO T_10199000(D, T, P, V) VALUES('2011-05-13', '10:11:18.0000', 280.85, 25.0);
> INSERT INTO T_10199000(D, T, P, V) VALUES('2011-05-13', '10:11:18.0001', 280.85, 25.0);
>
> In this example,
> - 3 contiguous ranges :
> -- from (2011-05-13, 10:11:11) to
> (2011-05-13, 10:11:12)
> -- from (2011-05-13, 10:11:15) to
> (2011-05-13, 10:11:15)
> -- from (2011-05-13, 10:11:18) to
> (2011-05-13, 10:11:18)
>
> - 2 empty ranges:
> -- from (2011-05-13, 10:11:13) to
> (2011-05-13, 10:11:14)
> -- from (2011-05-13, 10:11:16) to
> (2011-05-13, 10:11:17)
>
>
> [2] Next, for each range,
> if it is a contiguous range, I would retrieve all the records belonging to that range.
> if it is an empty range, I would do request data for that emtpy range from external data source and insert them into that missing range.
>
> [3] Finally, after iterating all the ranges, the table T_10199000 will have no empty range left.
>
>
> I guess Firebird SQL could do this and maybe quite easily and efficiently.
>
> Can someone please write SQL code for the above job?
>
> Thanks a lot.
>
> HaeRim
>