--- 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
>

simplest way is creating stored procedure which generate timestamp

CREATE PROCDURE GET_SECONDS_RANGE(ADATE_FROM TIMESTAMP, ADATE_TO TIMESTAMP)
RETURNS (
OUT_DATETIME TIMESTAMP
)
AS
DECLARE VARIABLE A TIMESTAMP;
BEGIN
A = ADATETIME_FROM;
WHILE (A<=ADATETIME_TO) DO
BEGIN
SUSSPEND;
A = DATEADD(1 SECOND TO A);
END
END;


and then you can do select

select
R.OUT_DATETIME, CASE WHEN T.D IS NULL THEN 'EMPTY' ELSE 'OK' END
FROM
GET_SECONDS_RANGE('2011-05-13, 10:00:00', '2011-05-13, 11:00:00') R
LEFT JOIN T_10199000 T ON R.OUT_DATETIME=T.D+T.T

of course you can here not ranges only places where data are stored and where are not
you can modify this by adding sub select and getting real ranges

of course simplest is creating whole as stored procedure

Karol Bieniaszewski