Finding a percentage by hours from irregular dates

Question

I have a table like the following, each entry changes the STATUS value at a given time. The status can be repeated because other columns have sub-status information.

How can I get the percentage time for each status, say, an hour?

NAME STATUS_CHANGE_TIME STATUS foo 15-MAY-11 18:52 A foo 15-MAY-11 18:38 A foo 15-MAY-11 18:33 B foo 15-MAY-11 16:53 A foo 15-MAY-11 16:47 B foo 15-MAY-11 13:37 A foo 15-MAY-11 13:33 C foo 15-MAY-11 10:23 C foo 15-MAY-11 10:17 A foo ... 

Desired income:

 HH24 STATUS PERCENT 10 ... 11 C 100 (No entries; last change was to C) 12 C 100 "" "" 13 C 62 13 A 38 (From C to A at :37 with 23 mins left; 23/60 ~ 38%) 14 A 100 15 A 100 16 A 90 (= A for first 47 minutes, then for another 7) 16 B 10 (16:53 - 16:47 = 6 minutes or 10% of an hour) 17 A 100 18 ... etc. 

Answer 1

Great question, it was an interesting task!

What you need is an auxiliary table to store each time division (in this case, the clock), and then join it, where status updates overlap. LEAD() can capture the next status record to check when it was, and GREATEST() and LEAST() can determine what time is applicable to start and end the state for each hour.

Of course, this is much easier to explain in the example. The following is the HOURS table:

 SQL> CREATE TABLE hours (HOUR NUMBER(2), start_m date, end_m date); Table created. SQL> BEGIN 2 FOR i IN 0..23 LOOP 3 INSERT INTO hours VALUES(i, to_date(lpad(i, 2, '0')||':00:00', 'HH24:MI:SS') 4 , to_date(lpad(i, 2, '0')||':59:59', 'HH24:MI:SS')); 5 END loop; 6 COMMIT; 7 END; 8 / PL/SQL procedure successfully completed. 

Below is only information about your test data from your question.

 SQL> CREATE TABLE status_updates (NAME VARCHAR2(3), status_change_time DATE, status CHAR(1)); Table created. SQL> INSERT INTO status_updates VALUES ('foo',TO_DATE('15-MAY-11 18:52', 'DD-MON-RR HH24:MI:SS'), 'A'); 1 row created. SQL> INSERT INTO status_updates VALUES ('foo',TO_DATE('15-MAY-11 18:38', 'DD-MON-RR HH24:MI:SS'), 'A'); 1 row created. SQL> INSERT INTO status_updates VALUES ('foo',TO_DATE('15-MAY-11 18:33', 'DD-MON-RR HH24:MI:SS'), 'B'); 1 row created. SQL> INSERT INTO status_updates VALUES ('foo',TO_DATE('15-MAY-11 16:53', 'DD-MON-RR HH24:MI:SS'), 'A'); 1 row created. SQL> INSERT INTO status_updates VALUES ('foo',TO_DATE('15-MAY-11 16:47', 'DD-MON-RR HH24:MI:SS'), 'B'); 1 row created. SQL> INSERT INTO status_updates VALUES ('foo',TO_DATE('15-MAY-11 13:37', 'DD-MON-RR HH24:MI:SS'), 'A'); 1 row created. SQL> INSERT INTO status_updates VALUES ('foo',TO_DATE('15-MAY-11 13:33', 'DD-MON-RR HH24:MI:SS'), 'C'); 1 row created. SQL> INSERT INTO status_updates VALUES ('foo',TO_DATE('15-MAY-11 10:23', 'DD-MON-RR HH24:MI:SS'), 'C'); 1 row created. SQL> INSERT INTO status_updates VALUES ('foo',TO_DATE('15-MAY-11 10:17', 'DD-MON-RR HH24:MI:SS'), 'A'); 1 row created. SQL> commit; Commit complete. 

Now here is the select statement to get the required percentages.

 SELECT t.NAME, t.HOUR, t.status, sum(round((status_end_h-start_status_h)*24*100)) per_cent FROM ( SELECT A.NAME , A.status , A.status_change_time , A.next_change_time , b.HOUR , greatest(status_change_time, trunc(status_change_time)+(b.start_m-trunc(b.start_m))) start_status_h , least(next_change_time, trunc(next_change_time)+(b.end_m-trunc(b.end_m))) status_end_h FROM ( SELECT NAME , status , status_change_time , lead(status_change_time) OVER (ORDER BY NAME, status_change_time) next_change_time FROM status_updates ) A, hours b WHERE TO_CHAR(b.start_m, 'HH24:MI:SS') BETWEEN TO_CHAR(A.status_change_time, 'HH24:MI:SS') AND TO_CHAR(A.next_change_time, 'HH24:MI:SS') OR TO_CHAR(b.end_m, 'HH24:MI:SS') BETWEEN TO_CHAR(A.status_change_time, 'HH24:MI:SS') AND TO_CHAR(A.next_change_time, 'HH24:MI:SS') OR (TO_CHAR(A.status_change_time, 'HH24:MI:SS') BETWEEN TO_CHAR(b.start_m, 'HH24:MI:SS') AND TO_CHAR(b.end_m, 'HH24:MI:SS') AND TO_CHAR(A.next_change_time, 'HH24:MI:SS') BETWEEN TO_CHAR(b.start_m, 'HH24:MI:SS') AND TO_CHAR(b.end_m, 'HH24:MI:SS')) ) t GROUP BY t.NAME, t.HOUR, t.status ORDER BY t.HOUR; NAM HOUR S PER_CENT --- ---------- - ---------- foo 10 A 10 foo 10 C 62 foo 11 C 100 foo 12 C 100 foo 13 A 38 foo 13 C 62 foo 14 A 100 foo 15 A 100 foo 16 A 90 foo 16 B 10 foo 17 A 100 NAM HOUR S PER_CENT --- ---------- - ---------- foo 18 A 78 foo 18 B 8 13 rows selected.