Oracle Hourly Report subtract two fields by hour for given date range

Question

I am working with 1 table from Oracle. I know what I am asking is very complex. I am ready offer 100 points.

I need to find out how my users are still out each hour for given range.

It has following fields Out_ID,OFF_TIME,ON_TIME and Users_Affected

Query

SELECT
       OUT_ID,
       OFF_TIME,
       ON_TIME,
       USER_AFFECTED
From TableA
where OFF_TIME > '06-02-201 00:00:00' and ON_TIME < '06-02-2014 16:00:00'
order by 2

Output

OUT_ID OFF_TIME            ON_TIME             USER_AFFECTED
------ ------------------- ------------------- -------------
46791  06-02-2014 00:32:05 06-02-2014 01:15:57             1
46792  06-02-2014 01:32:07 06-02-2014 01:52:51            51
46793  06-02-2014 01:49:40 06-02-2014 01:52:51            10
46794  06-02-2014 03:31:11 06-02-2014 04:31:57             1
46795  06-02-2014 03:51:07 06-02-2014 06:18:52            19
46796  06-02-2014 04:42:44 06-02-2014 05:40:47             1
46809  06-02-2014 06:13:24 06-02-2014 06:23:05             1
46811  06-02-2014 06:44:48 06-02-2014 09:18:59             1
46812  06-02-2014 06:45:55 06-02-2014 06:46:34          1933
46813  06-02-2014 06:57:40 06-02-2014 07:01:41             1
46814  06-02-2014 07:00:14 06-02-2014 07:58:20             1
46815  06-02-2014 07:51:07 06-02-2014 09:03:48             1
46816  06-02-2014 07:59:26 06-02-2014 08:30:13             1
46817  06-02-2014 08:35:09 06-02-2014 10:02:13             1
46818  06-02-2014 08:46:45 06-02-2014 09:28:04             3
46819  06-02-2014 09:01:03 06-02-2014 10:12:47             1
46820  06-02-2014 09:06:09 06-02-2014 10:28:46            23
46821  06-02-2014 09:27:15 06-02-2014 10:55:37             1
46822  06-02-2014 10:15:35 06-02-2014 11:57:48             1
46823  06-02-2014 10:17:39 06-02-2014 10:23:24             4
46824  06-02-2014 10:36:02 06-02-2014 10:44:24            99
46825  06-02-2014 10:39:11 06-02-2014 10:44:24            40
46826  06-02-2014 10:56:46 06-02-2014 11:54:41             1
46827  06-02-2014 11:07:33 06-02-2014 12:08:19             1
46829  06-02-2014 11:22:50 06-02-2014 13:35:36             1
46830  06-02-2014 11:24:56 06-02-2014 11:34:28             1
46831  06-02-2014 11:30:16 06-02-2014 12:15:54             1
46832  06-02-2014 11:32:09 06-02-2014 12:43:55             1
46833  06-02-2014 11:57:44 06-02-2014 12:44:29             1
46834  06-02-2014 12:04:48 06-02-2014 12:54:32           238
46835  06-02-2014 12:04:54 06-02-2014 12:54:32            22
46836  06-02-2014 13:21:39 06-02-2014 13:58:58             1
46837  06-02-2014 13:24:50 06-02-2014 15:41:33             1
46838  06-02-2014 13:26:02 06-02-2014 14:09:57             1
46839  06-02-2014 13:53:23 06-02-2014 14:35:51             1
46840  06-02-2014 13:59:56 06-02-2014 14:02:52             1
46841  06-02-2014 14:04:17 06-02-2014 15:21:43             1
46842  06-02-2014 14:20:52 06-02-2014 15:02:57             4
46843  06-02-2014 14:28:40 06-02-2014 16:15:54             1
46844  06-02-2014 14:36:43 06-02-2014 14:44:38             1
46845  06-02-2014 14:37:26 06-02-2014 15:27:17             1
46847  06-02-2014 15:13:32 06-02-2014 16:04:04             1
46848  06-02-2014 15:23:53 06-02-2014 16:44:25             1
46849  06-02-2014 15:28:14 06-02-2014 15:29:15             1
46850  06-02-2014 15:35:46 06-02-2014 17:41:09             1

Output I would like to see, and what I tried is.

I made 2 query. One for OUT_ID OFF_TIME and second for OUT_ID ON_TIME.

Query1

SELECT trunc(OFF_TIME,'HH') as OFF_HOUR, 
       sum(USER_AFFECTED) AS Num_Cust_OFF
  From tableA
 where OFF_TIME > '06-02-2014 00:00:00' 
   and ON_TIME < '06-02-2014 16:00:00' 
 group by trunc(OFF_TIME,'HH') 
     order by OFF_HOUR

Output for all user off by hour.

OFF_HOUR            NUM_CUST_OFF
------------------- ------------
06-02-2014 00:00:00            1
06-02-2014 01:00:00           61
06-02-2014 03:00:00           20
06-02-2014 04:00:00            1
06-02-2014 06:00:00         1936
06-02-2014 07:00:00            3
06-02-2014 08:00:00            4
06-02-2014 09:00:00           25
06-02-2014 10:00:00          145
06-02-2014 11:00:00            6
06-02-2014 12:00:00          260
06-02-2014 13:00:00            5
06-02-2014 14:00:00            7
06-02-2014 15:00:00            1

14 rows selected.

Querty2 Userback ON

select trunc(ON_TIME,'HH') as ON_HOUR, 
       sum(USER_AFFECTED) AS Num_Cust_ON
  From TableA
 where OFF_TIME > '06-02-2014 00:00:00' 
   and ON_TIME < '06-02-2014 16:00:00' 
 group by trunc(ON_TIME,'HH') 
 order by ON_HOUR

Output for all users on by hour

ON_HOUR             NUM_CUST_ON
------------------- -----------
06-02-2014 01:00:00          62
06-02-2014 04:00:00           1
06-02-2014 05:00:00           1
06-02-2014 06:00:00        1953
06-02-2014 07:00:00           2
06-02-2014 08:00:00           1
06-02-2014 09:00:00           5
06-02-2014 10:00:00         169
06-02-2014 11:00:00           3
06-02-2014 12:00:00         264
06-02-2014 13:00:00           2
06-02-2014 14:00:00           4
06-02-2014 15:00:00           8

13 rows selected.

What I do from now is do the calculation in excel, by taking difference of Each Hour.

NUM_CUST_OFF - NUM_CUST_ON at 06-02-2014 00:00:00 if there is carry over then add to next hour and take difference again.

So I get following in excel, I get following.

TIME_HOURLY     USER_STILL_AFFECTED
-------------   -------------------
6/2/2014 0:00   1
6/2/2014 1:00   0
6/2/2014 3:00   20
6/2/2014 4:00   20
6/2/2014 5:00   19
6/2/2014 6:00   2
6/2/2014 7:00   3
6/2/2014 8:00   6
6/2/2014 9:00   26
6/2/2014 10:00  2
6/2/2014 11:00  5
6/2/2014 12:00  1
6/2/2014 13:00  4
6/2/2014 14:00  7
6/2/2014 15:00  0

I am probably just blatantly missing this, but can you better explain what is going on? I don't understand how 3:00 and 4:00 are both = 20. — Craig, Jun 19 '14 at 16:54
Np :), you have to subtract OFF - ON = result(subtract 1:00 HOUR from 1:00 Hour, 2:00 hour from 2:00 Hour..you have to match and subtract. The result you get you have to carry over this to next hour...it should be 0 for last hour. — Mowgli, Jun 19 '14 at 18:21

GangBunTu · Accepted Answer · 2014-06-20T01:44:03.797

2

Use Analytic Function SUM (thought about LAG first, but unsure whether it can process on-the-fly field):

SELECT T1.TIME_HOURLY,
    SUM(NVL(OFF1.Num_Cust_OFF, 0)-NVL(ON1.Num_Cust_ON,0)) 
          OVER (ORDER BY T1.TIME_HOURLY) AS USER_STILL_AFFECTED
FROM (
    SELECT trunc(OFF_TIME,'HH') as TIME_HOURLY From tableA
    UNION -- this will get distinct result
    SELECT trunc(ON_TIME,'HH') as TIME_HOURLY From tableA
) T1
LEFT JOIN (
    SELECT trunc(OFF_TIME,'HH') as OFF_HOUR, 
        sum(USER_AFFECTED) AS Num_Cust_OFF
    From tableA
    where OFF_TIME > '06-02-2014 00:00:00' 
        and ON_TIME < '06-02-2014 16:00:00' 
    group by trunc(OFF_TIME,'HH') 
) OFF1 ON T1.TIME_HOURLY = OFF1.OFF_HOUR
LEFT JOIN (
    select trunc(ON_TIME,'HH') as ON_HOUR, 
        sum(USER_AFFECTED) AS Num_Cust_ON
    From TableA
    where OFF_TIME > '06-02-2014 00:00:00' 
        and ON_TIME < '06-02-2014 16:00:00' 
    group by trunc(ON_TIME,'HH') 
) ON1 ON T1.TIME_HOURLY = ON1.ON_HOUR
ORDER BY T1.TIME_HOURLY

Above is untested and not optimized, but should give a hint towards proper solution.

Possible optimization: Subquery T1 may be dropped, relation between OFF1 and ON1 becomes FULL JOIN and any reference to T1.TIME_HOURLY replaced with COALESCE(OFF1.OFF_HOUR, ON1.ON_HOUR).

edited Jun 20 '14 at 01:44

answered Jun 19 '14 at 17:53

GangBunTu

57
5

Thanks a lot, I really like your approach,very useful hints and idea. ) ON1 ON T1.TIME_HOURLY - ON1.ON_HOUR should be `=` intead of `-`. – user206168 Jun 19 '14 at 18:58
Didn't catch that typo. fixed. – GangBunTu Jun 20 '14 at 01:44
I have question, I have made 2 separate query. I am trying to join them now. I have already 1 long query but I need help adding 2 query's `USER_STILL_AFFECTED` How can I do that? – user206168 Jun 20 '14 at 17:41
something like this [link](http://stackoverflow.com/questions/20636192/output-of-two-different-queries-as-one-result-in-oracle-sql) – user206168 Jun 20 '14 at 17:50
Without knowing the detailed requirement I'd say use JOIN, wrapping USER_STILL_AFFECTED logic into a sub-query (just as T1, OFF1 and ON1). If long query already uses tableA beforehand, rewrite may be recommended for better performance - I can't help you much on rewrite though. – GangBunTu Jun 21 '14 at 02:16

Oracle Hourly Report subtract two fields by hour for given date range

1 Answers1