Hi all,
I've a table like this:
date, value
01/01/2008, 3
01/02/2008, 4
02/03/2008, 7

and I wish to find with one query the difference (in days) from all
the consecutive intervals (01/02 - 01/01 and 02/03 - 01/02 in this
example).

Someone could help me please?
Thanks all,
DM

DM wrote:
> Hi all,
> I've a table like this:
> date, value
> 01/01/2008, 3
> 01/02/2008, 4
> 02/03/2008, 7
>
> and I wish to find with one query the difference (in days) from all
> the consecutive intervals (01/02 - 01/01 and 02/03 - 01/02 in this
> example).
>
> Someone could help me please?
> Thanks all,
> DM
Take a look at the OLAP functions. Especially the ROWS PRECEEDING
clause. They allow you to dip into trailing or leading rows.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

>> I've a table like this: <<

Please post DDL and do not use reserved words for column names. Let's
cleanup what you have now:

CREATE TABLE Foobar
(foo_date DATE NOT NULL PRIMARY KEY, -- wild guess
bar_value INTEGER NOT NULL);

Also, Standard SQL allows only "yyyy-mm-dd" format for dates. This is
also true for many other ANSI/ISO standards, so you might want to pick
up a good programming habit now. What you posted is ambiguous -- is it
traditional US or UK format?

>> I wish to find with one query the difference (in days) from all the consecutive intervals (01/02 - 01/01 and 02/03 - 01/02 in this example). <<

There are some solutions with self-joins, but the real answer is that
if you want those intervals, you should design your table correctly.
Time is a continuum, so you need to show the days that a "bar_value"
was valid.

CREATE TABLE Foobar
(foo_start_date DATE NOT NULL,
foo_end_date DATE -- null is current
CHECK (foo_start_date <= foo_end_date),
PRIMARY KEY (foo_start_date),
bar_value INTEGER NOT NULL);

I have to guess at when intervals end -- on the next day or before?

INSERT INTO Foobar
VALUES ('2008-01-01', '2008-01-01', 3),
('2008-01-02', '2008-02-02', 4),
('2008-02-03', NULL, 7 );

Here is the SQL-99 code; you might have to change it a bit:

SELECT foo_start_date, bar_value,
(COALESCE (foo_end_date, CURRENT_DATE) - (foo_start_date +
INTERVAL '1' DAY)) DAY
AS bar_duration
FROM Foobar;