Help with self-join

[Guest_imported]

Hi,

I have a table with two columns, which contains when a device was turned off.

TIME STATUS
10:23 ON
10:44 OFF
11:24 ON
11:29 OFF
12:56 ON
12:59 OFF


I want to create another table using a select statement (no othere privileges in that database) that will contain how long the device was switched on.

TIME_ON TIME_OFF DURATION
10:23 10:44 21
11:24 11:29 5
12:56 12:59 3


What is the select statement I should use? Thanks.

 

[ThomVF]

Are there any other columns in the table? With just 2 columns (no keys), I am not sure you can do this with a query/join.

You can probably do it with PL/SQL and cursors but the other problem is getting the data back from a SELECT in the order you show. Not knowing if you have a "Device" ID to use that will allow you to find the OFF record for a specific ON record, I cannot give you a solution.
"Helping others to help themselves..."
=================================
Thomas V. Flaherty Jr.
Birch Hill Technology Group, Inc.

http://www.birchhillgroup.com

 

[Guest_imported]

take a deep breath...

select c.time, d.time, datediff(mi,c.time,d.time) time_on from (select count(*) rowid, a.time from (select time from table where status = 'on') a, (select time from table where status = 'on') b where a.time >= b.time group by a.time) c,
(select count(*) rowid, a.time from (select time from table where status = 'off') a, (select time from table where status = 'off') b where a.time >= b.time group by a.time) d,
where c.rowid = d.rowid

that's sybase sql, don't know if it's different in ANSI, probaly the datediff part is.

ThomVF: assuming it comes from the same device.

 

[angiole]

I have a solution to propose in Oracle, but requires some assumptions, if that's your DB.
AA 8~)

 

[ThomVF]

I was assuming that the ON/OFF pairs were for different devices, not a single device. "Helping others to help themselves..."
=================================
Thomas V. Flaherty Jr.
Birch Hill Technology Group, Inc.

http://www.birchhillgroup.com