| flattening an event table [message #18526] |
Mon, 04 February 2002 08:27  |
Dean
Messages: 34
Registered: August 2000
|
Member |
|
|
I need to report from an application that writes to an event table
so that the resultant table looks like this:
DATE USER TIME_STAMP EVENT_TYPE
02/01/02 BOB 08:00:00 ON
02/01/02 BOB 12:01:00 OFF
02/01/02 BOB 12:59:00 ON
02/01/02 BOB 17:01:00 OFF
02/01/02 FRANK 07:59:00 ON
02/01/02 FRANK 12:01:00 OFF
ETC....
Can anyone suggest a method to flatten this table so that
the amount of time spent "on" can be calculated? The events will always occur in pairs.
|
|
|
|
|
|
| Re: flattening an event table [message #18529 is a reply to message #18526] |
Mon, 04 February 2002 09:37  |
Jon
Messages: 483
Registered: May 2001
|
Senior Member |
|
|
You'll want to tweak this a little bit to get the Event_Date and concatenate it with the times, and the To_Date them, etc., but here is the basic idea..
SELECT A.NAME, A.TIME_STAMP DATE_ON,
(SELECT MIN(TIME_STAMP) FROM TABLE1 C WHERE A.NAME = C.NAME
AND C.EVENT_TYPE= 'OFF' AND
TO_CHAR(C.EVENT_DATE,'YYYYMMDD')||C.TIME_STAMP > TO_CHAR(A.EVENT_DATE,'YYYYMMDD')||A.TIME_STAMP) DATE_OFF
FROM TABLE1 A WHERE EVENT_TYPE = 'ON';
EVENT_DATE NAME TIME_STA EVE
---------- -------------------- -------- ---
02/01/2002 BOB 08:00:00 ON
02/01/2002 BOB 12:01:00 OFF
02/01/2002 BOB 12:59:00 ON
02/01/2002 BOB 17:01:00 OFF
02/01/2002 FRANK 07:59:00 ON
02/01/2002 FRANK 12:01:00 OFF
6 rows selected.
14:33:40 ==> SELECT A.NAME, A.TIME_STAMP DATE_ON,
14:33:50 2 (SELECT MIN(TIME_STAMP) FROM TABLE1 C WHERE A.NAME = C.NAME
14:33:50 3 AND C.EVENT_TYPE= 'OFF' AND
14:33:50 4 TO_CHAR(C.EVENT_DATE,'YYYYMMDD')||C.TIME_STAMP > TO_CHAR(A.EVENT_DATE,'YYYYMMDD')||A.TIME_STAMP) DATE_OFF
14:33:51 5 FROM TABLE1 A WHERE EVENT_TYPE = 'ON';
NAME DATE_ON DATE_OFF
-------------------- -------- --------
BOB 08:00:00 12:01:00
BOB 12:59:00 17:01:00
FRANK 07:59:00 12:01:00
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
