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