SQL puzzle

From: Karsten Schmidt <groups_at_karsten-schmidt.com>
Date: 15 Feb 2002 06:15:52 -0800
Message-ID: <c6711ac4.0202150615.6099275c@posting.google.com>

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 Received on Fri Feb 15 2002 - 08:15:52 CST