Home » SQL & PL/SQL » SQL & PL/SQL » flattening an event table
flattening an event table [message #18526] Mon, 04 February 2002 08:27 Go to next message
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 #18527 is a reply to message #18526] Mon, 04 February 2002 08:43 Go to previous messageGo to next message
Jon
Messages: 483
Registered: May 2001
Senior Member
How about?

Date
Name
Time on
Time off
Re: flattening an event table [message #18529 is a reply to message #18526] Mon, 04 February 2002 09:37 Go to previous messageGo to next message
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
Re: flattening an event table [message #18533 is a reply to message #18529] Mon, 04 February 2002 15:52 Go to previous message
Dean
Messages: 34
Registered: August 2000
Member
Excellent, thank you.
Previous Topic: suresh
Next Topic: Partitioning and Optimizer Mode
Goto Forum:
  


Current Time: Fri Sep 17 10:52:32 CDT 2021