Forum Stats

  • 3,757,572 Users
  • 2,251,247 Discussions
  • 7,869,867 Comments

Discussions

Query: minute based result

708049
708049 Member Posts: 6
edited Jun 23, 2009 7:37AM in SQL & PL/SQL
Hi,

I'm working on reporting and got an issue:

There is a request to report minute/hour based data. The time should be displayed even if there is no value in this (short) time frame (e.g. minute). Are there any capabilities solving this issue wih Oracle functionality? I hope you can recognize what I mean. Here is an example of the desired outout:

05:00:00 150
05:01:00 90
05:02:00 0 (<--- displayed even if no value is given)
05:03:00 120

Would be really nice to hear some ideas on how to solve this issue.



Beste regards

Best Answer

  • 630199
    630199 Member Posts: 483
    Accepted Answer
    Maybe something like the following:
    SQL> variable until_datetime varchar2(20);
    SQL> variable  from_datetime varchar2(20);
    SQL> exec :from_datetime := '18/06/2009 07:40:00';
    
    PL/SQL procedure successfully completed.
    
    SQL> exec :until_datetime := '18/06/2009 07:50:00';
    
    PL/SQL procedure successfully completed.
    
    SQL> WITH test_tab AS
      2       (SELECT TO_DATE ('2009-06-18 07:44:13',
      3                        'YYYY-MM-DD HH24:MI:SS') time_col,
      4               'XXX-0987' session_id
      5          FROM DUAL
      6        UNION ALL
      7        SELECT TO_DATE ('2009-06-18 07:44:38',
      8                        'YYYY-MM-DD HH24:MI:SS') time_col,
      9               'XXX-1234' session_id
     10          FROM DUAL
     11        UNION ALL
     12        SELECT TO_DATE ('2009-06-18 07:44:51',
     13                        'YYYY-MM-DD HH24:MI:SS') time_col,
     14               'XXX-0987' session_id
     15          FROM DUAL
     16        UNION ALL
     17        SELECT TO_DATE ('2009-06-18 07:45:11',
     18                        'YYYY-MM-DD HH24:MI:SS') time_col,
     19               'XXX-1234' session_id
     20          FROM DUAL
     21        UNION ALL
     22        SELECT TO_DATE ('2009-06-18 07:45:22',
     23                        'YYYY-MM-DD HH24:MI:SS') time_col,
     24               'XXX-0987' session_id
     25          FROM DUAL)
     26  SELECT   to_char(b.time_col, 'DD-MM-YYYY HH24:MI') time_col, count(a1.session_id) count_session
    
     27      FROM (SELECT       TRUNC (TO_DATE (:from_datetime,
     28                                         'DD/MM/YYYY HH24:MI:SS'),
     29                                'MI'
     30                               )
     31                       + (LEVEL - 1) / (24 * 60) time_col
     32                  FROM DUAL
     33            CONNECT BY LEVEL <=
     34                              (  TRUNC (TO_DATE (:until_datetime,
     35                                                 'DD/MM/YYYY HH24:MI:SS'
     36                                                ),
     37                                        'MI'
     38                                       )
     39                               - TRUNC (TO_DATE (:from_datetime,
     40                                                 'DD/MM/YYYY HH24:MI:SS'
     41                                                ),
     42                                        'MI'
     43                                       )
     44                              )
     45                            * (24 * 60)
     46                          + 1) b,
     47           (SELECT trunc(time_col, 'MI') time_col, session_id FROM test_tab) a1
     48     WHERE a1.time_col(+) = b.time_col
     49     group by to_char(b.time_col, 'DD-MM-YYYY HH24:MI')
     50  ORDER BY to_char(b.time_col, 'DD-MM-YYYY HH24:MI')
     51  /
    
    TIME_COL         COUNT_SESSION
    ---------------- -------------
    18-06-2009 07:40             0
    18-06-2009 07:41             0
    18-06-2009 07:42             0
    18-06-2009 07:43             0
    18-06-2009 07:44             3
    18-06-2009 07:45             2
    18-06-2009 07:46             0
    18-06-2009 07:47             0
    18-06-2009 07:48             0
    18-06-2009 07:49             0
    18-06-2009 07:50             0
    
    11 rows selected.
    
    SQL> 
    Regards,
    Jo
«1

Answers

  • 673860
    673860 Member Posts: 902
    Hi welcome to the forum please mention your DB version and some sample input and required output.

    what is second column you have mentioned.
  • 708049
    708049 Member Posts: 6
    Thanks for welcome me.

    Our DB version is 10.2

    The second column ist just a count of occurrences like:

    SELECT trunc_date, count(*) FROM t_page_hits

    t_page_hits is structured like this:

    n_id (number)
    c_url (string)
    c_session (string)
    d_created (date)


    I mentioned the second column beacuse if there are no occurrences, the date will not be delivered in the result.
  • 630199
    630199 Member Posts: 483
    edited Jun 22, 2009 3:34AM
    Maybe you can work on the lines of the following example:
    SQL> WITH test_tab
      2         AS (SELECT   TO_DATE ('05:00:00', 'HH24:MI:SS') time_col, 10 value_col
      3               FROM   DUAL
      4             UNION ALL
      5             SELECT   TO_DATE ('05:03:00', 'HH24:MI:SS') time_col, 20 value_col
      6               FROM   DUAL
      7             UNION ALL
      8             SELECT   TO_DATE ('05:04:00', 'HH24:MI:SS') time_col, 30 value_col
      9               FROM   DUAL
     10             UNION ALL
     11             SELECT   TO_DATE ('05:06:00', 'HH24:MI:SS') time_col, 10 value_col
     12               FROM   DUAL)-- end of test_data
     13  SELECT   TO_CHAR (time_col, 'hh24:MI:SS') time_col, value_col
     14    FROM   test_tab
     15  /
    
    TIME_COL  VALUE_COL
    -------- ----------
    05:00:00         10
    05:03:00         20
    05:04:00         30
    05:06:00         10
    
    4 rows selected.
    
    SQL> WITH test_tab
      2         AS (SELECT   TO_DATE ('05:00:00', 'HH24:MI:SS') time_col, 10 value_col
      3               FROM   DUAL
      4             UNION ALL
      5             SELECT   TO_DATE ('05:03:00', 'HH24:MI:SS') time_col, 20 value_col
      6               FROM   DUAL
      7             UNION ALL
      8             SELECT   TO_DATE ('05:04:00', 'HH24:MI:SS') time_col, 30 value_col
      9               FROM   DUAL
     10             UNION ALL
     11             SELECT   TO_DATE ('05:06:00', 'HH24:MI:SS') time_col, 10 value_col
     12               FROM   DUAL) -- end of test_data
     13    SELECT   TO_CHAR (b.time_col, 'HH24:MI:SS') time_column,
     14             NVL (a1.value_col, 0) value_col
     15      FROM   (    SELECT   min_time_col + (LEVEL - 1) / (24 * 60) time_col
     16                    FROM   (SELECT   MIN (time_col) min_time_col,
     17                                     MAX (time_col) max_time_col
     18                              FROM   test_tab)
     19              CONNECT BY   LEVEL <=
     20                              ( (max_time_col - min_time_col) * (24 * 60)) +1) b,
     21             test_tab a1
     22     WHERE   a1.time_col(+) = b.time_col
     23  ORDER BY   b.time_col
     24  /
    
    TIME_COL  VALUE_COL
    -------- ----------
    05:00:00         10
    05:01:00          0          -- > Value absent in the table
    05:02:00          0          -- > Value absent in the table
    05:03:00         20
    05:04:00         30
    05:05:00          0          -- > Value absent in the table
    05:06:00         10
    
    7 rows selected.
    
    SQL>
    Assumed you are not considering Date part of your date_column.

    Hope this helps.

    Regards,
    Jo

    Edit: Improved readability of query.
    630199
  • 708049
    708049 Member Posts: 6
    Thank you, Joice John. This was helpful to me, but I need to act on my date column. These dates are written directly to database with seconds. So I need to catch the count between second 0 and second 59 on each minute.

    I think I will try your code and place an inner select preparing the data table to deliver count with rounded minutes.


    Thanks and regards
  • 708049
    708049 Member Posts: 6
    Are there any other approaches to resolve this issue?

    I tried the given stmt and it works fine for itself, but it does not resolve my problem. There are two important points:
    1) My date-column is not truncated to minutes
    2) start and end time should not be taken from table but from variables (:from, :until)

    Altering the given test_tab from above to have date values with seconds not equal to zero (eg. 05:00:11) results in value=0 for 05:00:00.
  • 630199
    630199 Member Posts: 483
    Can you post a few sample data that your table holds? And based on that sample data post your desired output.

    Regards,
    Jo
  • 708049
    708049 Member Posts: 6
    Hi,

    here is the initial position:


    /* Formatted on 2009/06/22 11:29 (Formatter Plus v4.8.8) */
    WITH test_tab AS
    (SELECT TO_DATE ('2009-06-18 07:44:13',
    'YYYY-MM-DD HH24:MI:SS') time_col,
    'XXX-0987' session_id
    FROM DUAL
    UNION ALL
    SELECT TO_DATE ('2009-06-18 07:44:38',
    'YYYY-MM-DD HH24:MI:SS') time_col,
    'XXX-1234' session_id
    FROM DUAL
    UNION ALL
    SELECT TO_DATE ('2009-06-18 07:44:51',
    'YYYY-MM-DD HH24:MI:SS') time_col,
    'XXX-0987' session_id
    FROM DUAL
    UNION ALL
    SELECT TO_DATE ('2009-06-18 07:45:11',
    'YYYY-MM-DD HH24:MI:SS') time_col,
    'XXX-1234' session_id
    FROM DUAL
    UNION ALL
    SELECT TO_DATE ('2009-06-18 07:45:22',
    'YYYY-MM-DD HH24:MI:SS') time_col,
    'XXX-0987' session_id
    FROM DUAL)
    SELECT *
    FROM test_tab

    results in:

    TIME_COL SESSION_ID
    18.06.2009 07:44:13 XXX-0987
    18.06.2009 07:44:38 XXX-1234
    18.06.2009 07:44:51 XXX-0987
    18.06.2009 07:45:11 XXX-1234
    18.06.2009 07:45:22 XXX-0987



    Desired output with variables:

    :from --> 2009-06-18 07:40:00
    :until --> 2009-06-18 07:50:00

    TIME COUNT
    07:40 0
    07:41 0
    07:42 0
    07:43 0
    07:44 3
    07:45 2
    07:46 0
    07:47 0
    07:48 0
    07:49 0
    07:50 0

    Maybe it is possible to reduce the time to HH24:MM but it would also be applicable to have the time formatted as HH24:MM:SS (with seconds as double-zero).

    Since these requirement meets us from time to time, I would like to transfer a possible solution into a stored procedure.
  • 630199
    630199 Member Posts: 483
    Accepted Answer
    Maybe something like the following:
    SQL> variable until_datetime varchar2(20);
    SQL> variable  from_datetime varchar2(20);
    SQL> exec :from_datetime := '18/06/2009 07:40:00';
    
    PL/SQL procedure successfully completed.
    
    SQL> exec :until_datetime := '18/06/2009 07:50:00';
    
    PL/SQL procedure successfully completed.
    
    SQL> WITH test_tab AS
      2       (SELECT TO_DATE ('2009-06-18 07:44:13',
      3                        'YYYY-MM-DD HH24:MI:SS') time_col,
      4               'XXX-0987' session_id
      5          FROM DUAL
      6        UNION ALL
      7        SELECT TO_DATE ('2009-06-18 07:44:38',
      8                        'YYYY-MM-DD HH24:MI:SS') time_col,
      9               'XXX-1234' session_id
     10          FROM DUAL
     11        UNION ALL
     12        SELECT TO_DATE ('2009-06-18 07:44:51',
     13                        'YYYY-MM-DD HH24:MI:SS') time_col,
     14               'XXX-0987' session_id
     15          FROM DUAL
     16        UNION ALL
     17        SELECT TO_DATE ('2009-06-18 07:45:11',
     18                        'YYYY-MM-DD HH24:MI:SS') time_col,
     19               'XXX-1234' session_id
     20          FROM DUAL
     21        UNION ALL
     22        SELECT TO_DATE ('2009-06-18 07:45:22',
     23                        'YYYY-MM-DD HH24:MI:SS') time_col,
     24               'XXX-0987' session_id
     25          FROM DUAL)
     26  SELECT   to_char(b.time_col, 'DD-MM-YYYY HH24:MI') time_col, count(a1.session_id) count_session
    
     27      FROM (SELECT       TRUNC (TO_DATE (:from_datetime,
     28                                         'DD/MM/YYYY HH24:MI:SS'),
     29                                'MI'
     30                               )
     31                       + (LEVEL - 1) / (24 * 60) time_col
     32                  FROM DUAL
     33            CONNECT BY LEVEL <=
     34                              (  TRUNC (TO_DATE (:until_datetime,
     35                                                 'DD/MM/YYYY HH24:MI:SS'
     36                                                ),
     37                                        'MI'
     38                                       )
     39                               - TRUNC (TO_DATE (:from_datetime,
     40                                                 'DD/MM/YYYY HH24:MI:SS'
     41                                                ),
     42                                        'MI'
     43                                       )
     44                              )
     45                            * (24 * 60)
     46                          + 1) b,
     47           (SELECT trunc(time_col, 'MI') time_col, session_id FROM test_tab) a1
     48     WHERE a1.time_col(+) = b.time_col
     49     group by to_char(b.time_col, 'DD-MM-YYYY HH24:MI')
     50  ORDER BY to_char(b.time_col, 'DD-MM-YYYY HH24:MI')
     51  /
    
    TIME_COL         COUNT_SESSION
    ---------------- -------------
    18-06-2009 07:40             0
    18-06-2009 07:41             0
    18-06-2009 07:42             0
    18-06-2009 07:43             0
    18-06-2009 07:44             3
    18-06-2009 07:45             2
    18-06-2009 07:46             0
    18-06-2009 07:47             0
    18-06-2009 07:48             0
    18-06-2009 07:49             0
    18-06-2009 07:50             0
    
    11 rows selected.
    
    SQL> 
    Regards,
    Jo
  • 708049
    708049 Member Posts: 6
    Thank you very much, this a a working solution that covers all my needs.
  • 630199
    630199 Member Posts: 483
    You are welcome.

    Good Luck!!!!

    Regards,
    Jo
This discussion has been closed.