Thanks a lot for your answer, which I am trying hard to understand. Yet
it is pretty hard for me.

Many new terms that I don't know yet and I can't even find them from
docs like EmbedSQL.pdf and LangRef.pdf.

They are 'Empty', 'execute block', 'recursive CTE'.

Please give me some clues or links to study these words.

HR


--- In firebird-support@yahoogroups.com, Svein Erling Tysv?
<svein.erling.tysvaer@...> wrote:

>
> Easily and efficiently? Well, not quite, but it is doable if your

table is smallish (alternatively, you could write a stored procedure,
execute block or possibly a recursive CTE.

>
> 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 /*whatever your primary key is, the point is to

ascertain such a row doesn't exist*/

> and t2.T - t1.T > 1.000000 / 86400 /*You might have to tweak a bit,

there are 86400 seconds in a day, but you might */

> /*not want 10:11:15.0000 and 10:11:16.0002 to be considered a gap. */
> union
> SELECT 'Contiguous', t1.T as MinEmpty, t2.T as MaxEmpty
> 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.000000 / 86400) /*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.000000 / 86400) and tNot2.T /*Just

after*/

> where tNot1.D is NULL /*None just before*/
> and tNot2.D is NULL /*None just after*/
> and t2.T - t1.T < 1.000000 / 86400
>
> HTH,
> Set
>
> -----Original Message-----
> From: firebird-support@yahoogroups.com

[mailto:firebird-support@yahoogroups.com] On Behalf Of ehaerim

> Sent: 19. mai 2011 06:43
> To: firebird-support@yahoogroups.com
> Subject: [firebird-support] How to select contiguous and empty ranges?
>
> 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
>