Re: SQL puzzle

only a hint, but no code:

did you consider the new analytic functions introduced with 8.1.6? You can do rather tricky things with impressive performance boosts.

On 15 Feb 2002 06:15:52 -0800, groups_at_karsten-schmidt.com (Karsten Schmidt) wrote:

>Hi all,
> I am trying to come up with a sql query that solves the following question:
>
>I have a table like
>
>DEVICE time status
>A 10:00 up
>A 10:30 up
>A 10:45 down
>A 10:48 up
>B 9:00 up
>B 10:00 up
>... etc, i guess you get the idea.
>the rows represent samples of device availability.
>
>I would like to have a result like
>Device status from to
>A up 10:00 10:45
>A down 10:45 10:48
>A up 10:48 (null)
>B up 9:00 (null)
>
>I have been trying to figure this out for quite a while, all i could
>come up with was a stored procedure that populates a table.
>
>I would really appreciate some help on this. maintaining that separate table
>is both slow and hard to keep in sync.
>
>This is for a datawarehousing application on Oracle 8.1.7.2 EE / HP 64 Bit
>
>Karsten

regards
Marc Blum
mailto:marc_at_marcblum.de
http://www.marcblum.de Received on Sat Feb 16 2002 - 12:28:53 CST