#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2013
    Posts
    5
    Rep Power
    0

    Group Records with less than one hour separation and count how many per group


    I'm new here, please HELP!...and since I'm new, I can't post any images to help explain.

    I'm trying to group sets of data based on time separations between records and then count how many records are in each group.

    In the example below, I want to return the count for each group of data, so Group 1=5, Group 2=5 and Group 3=5


    SELECT AREA_ID AS "AREA ID",
    LOC_ID AS "LOCATION ID",
    TEST_DATE AS "DATE",
    TEST_TIME AS "TIME"
    FROM MON_TEST_MASTER
    WHERE AREA_ID =89
    AND LOC_ID ='3015'
    AND TEST_DATE ='10/19/1994';


    AREA ID LOCATION ID DATE TIME
    89 3015 10/19/1994 8:00:22
    89 3015 10/19/1994 8:00:22
    89 3015 10/19/1994 8:19:55
    89 3015 10/19/1994 8:19:55
    89 3015 10/19/1994 8:41:22
    89 3015 10/19/1994 11:35:47
    89 3015 10/19/1994 11:35:47
    89 3015 10/19/1994 11:35:47
    89 3015 10/19/1994 11:35:47
    89 3015 10/19/1994 11:35:47
    89 3015 10/19/1994 15:13:46
    89 3015 10/19/1994 15:13:46
    89 3015 10/19/1994 15:13:46
    89 3015 10/19/1994 15:13:46
    89 3015 10/19/1994 15:13:46

    Group 1 = 8:00:22 to 8:41:22

    Group 2 = 11:35:47 to 11:35:47

    Group 3 = 15:13:46 to 15:13:46


    Keep in mind the times will always change, and sometime go over the one hour mark, but no group will have more then a one hour separation between records.

    I hope this makes sense...PLEASE HELP!
  2. #2
  3. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    857
    Rep Power
    388

    Cool


    Originally Posted by TheLos
    I'm new here, please HELP!...and since I'm new, I can't post any images to help explain.

    I'm trying to group sets of data based on . . .
    ... Etc ...
    I hope this makes sense...PLEASE HELP!
    Something like this:?
    Code:
    SQL> WITH my_tab (area_id, location_id, date_time)
      2    AS (SELECT 89, 3015, TO_DATE ('10/19/1994 08:00:22','mm/dd/yyyy hh24:mi:ss') FROM DUAL UNION ALL
      3        SELECT 89, 3015, TO_DATE ('10/19/1994 08:00:22','mm/dd/yyyy hh24:mi:ss') FROM DUAL UNION ALL
      4        SELECT 89, 3015, TO_DATE ('10/19/1994 08:19:55','mm/dd/yyyy hh24:mi:ss') FROM DUAL UNION ALL
      5        SELECT 89, 3015, TO_DATE ('10/19/1994 08:19:55','mm/dd/yyyy hh24:mi:ss') FROM DUAL UNION ALL
      6        SELECT 89, 3015, TO_DATE ('10/19/1994 08:41:22','mm/dd/yyyy hh24:mi:ss') FROM DUAL UNION ALL
      7        SELECT 89, 3015, TO_DATE ('10/19/1994 11:35:47','mm/dd/yyyy hh24:mi:ss') FROM DUAL UNION ALL
      8        SELECT 89, 3015, TO_DATE ('10/19/1994 11:35:47','mm/dd/yyyy hh24:mi:ss') FROM DUAL UNION ALL
      9        SELECT 89, 3015, TO_DATE ('10/19/1994 11:35:47','mm/dd/yyyy hh24:mi:ss') FROM DUAL UNION ALL
     10        SELECT 89, 3015, TO_DATE ('10/19/1994 11:35:47','mm/dd/yyyy hh24:mi:ss') FROM DUAL UNION ALL
     11        SELECT 89, 3015, TO_DATE ('10/19/1994 11:35:47','mm/dd/yyyy hh24:mi:ss') FROM DUAL UNION ALL
     12        SELECT 89, 3015, TO_DATE ('10/19/1994 15:13:46','mm/dd/yyyy hh24:mi:ss') FROM DUAL UNION ALL
     13        SELECT 89, 3015, TO_DATE ('10/19/1994 15:13:46','mm/dd/yyyy hh24:mi:ss') FROM DUAL UNION ALL
     14        SELECT 89, 3015, TO_DATE ('10/19/1994 15:13:46','mm/dd/yyyy hh24:mi:ss') FROM DUAL UNION ALL
     15        SELECT 89, 3015, TO_DATE ('10/19/1994 15:13:46','mm/dd/yyyy hh24:mi:ss') FROM DUAL UNION ALL
     16        SELECT 89, 3015, TO_DATE ('10/19/1994 15:13:46','mm/dd/yyyy hh24:mi:ss') FROM DUAL)
     17    SELECT 'Group #' || ROW_NUMBER () OVER (ORDER BY GROUP_ID)||' =' group_number, from_date, to_date, count#
     18      FROM (  SELECT TO_CHAR ( date_time, 'YYMMDDHH24') GROUP_ID
     19                   , MIN ( date_time) from_date, MAX ( date_time) to_date
     20                   , COUNT(*) count#
     21                FROM my_tab
     22               GROUP BY TO_CHAR ( date_time, 'YYMMDDHH24'))
     23* ORDER BY 1
    SQL> /
    
    GROUP_NUMBER         FROM_DATE                     TO_DATE                           COUNT#
    -------------------- ----------------------------- ----------------------------- ----------
    Group #1 =           19-Oct-1994 08:00:22          19-Oct-1994 08:41:22                   5
    Group #2 =           19-Oct-1994 11:35:47          19-Oct-1994 11:35:47                   5
    Group #3 =           19-Oct-1994 15:13:46          19-Oct-1994 15:13:46                   5
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2013
    Posts
    5
    Rep Power
    0
    The problem is it will not always be the same data, different date, different times...etc.

    This was just an example of the data.
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2013
    Posts
    5
    Rep Power
    0
    Originally Posted by LKBrwn_DBA
    Something like this:?
    Code:
    SQL> WITH my_tab (area_id, location_id, date_time)
      2    AS (SELECT 89, 3015, TO_DATE ('10/19/1994 08:00:22','mm/dd/yyyy hh24:mi:ss') FROM DUAL UNION ALL
      3        SELECT 89, 3015, TO_DATE ('10/19/1994 08:00:22','mm/dd/yyyy hh24:mi:ss') FROM DUAL UNION ALL
      4        SELECT 89, 3015, TO_DATE ('10/19/1994 08:19:55','mm/dd/yyyy hh24:mi:ss') FROM DUAL UNION ALL
      5        SELECT 89, 3015, TO_DATE ('10/19/1994 08:19:55','mm/dd/yyyy hh24:mi:ss') FROM DUAL UNION ALL
      6        SELECT 89, 3015, TO_DATE ('10/19/1994 08:41:22','mm/dd/yyyy hh24:mi:ss') FROM DUAL UNION ALL
      7        SELECT 89, 3015, TO_DATE ('10/19/1994 11:35:47','mm/dd/yyyy hh24:mi:ss') FROM DUAL UNION ALL
      8        SELECT 89, 3015, TO_DATE ('10/19/1994 11:35:47','mm/dd/yyyy hh24:mi:ss') FROM DUAL UNION ALL
      9        SELECT 89, 3015, TO_DATE ('10/19/1994 11:35:47','mm/dd/yyyy hh24:mi:ss') FROM DUAL UNION ALL
     10        SELECT 89, 3015, TO_DATE ('10/19/1994 11:35:47','mm/dd/yyyy hh24:mi:ss') FROM DUAL UNION ALL
     11        SELECT 89, 3015, TO_DATE ('10/19/1994 11:35:47','mm/dd/yyyy hh24:mi:ss') FROM DUAL UNION ALL
     12        SELECT 89, 3015, TO_DATE ('10/19/1994 15:13:46','mm/dd/yyyy hh24:mi:ss') FROM DUAL UNION ALL
     13        SELECT 89, 3015, TO_DATE ('10/19/1994 15:13:46','mm/dd/yyyy hh24:mi:ss') FROM DUAL UNION ALL
     14        SELECT 89, 3015, TO_DATE ('10/19/1994 15:13:46','mm/dd/yyyy hh24:mi:ss') FROM DUAL UNION ALL
     15        SELECT 89, 3015, TO_DATE ('10/19/1994 15:13:46','mm/dd/yyyy hh24:mi:ss') FROM DUAL UNION ALL
     16        SELECT 89, 3015, TO_DATE ('10/19/1994 15:13:46','mm/dd/yyyy hh24:mi:ss') FROM DUAL)
     17    SELECT 'Group #' || ROW_NUMBER () OVER (ORDER BY GROUP_ID)||' =' group_number, from_date, to_date, count#
     18      FROM (  SELECT TO_CHAR ( date_time, 'YYMMDDHH24') GROUP_ID
     19                   , MIN ( date_time) from_date, MAX ( date_time) to_date
     20                   , COUNT(*) count#
     21                FROM my_tab
     22               GROUP BY TO_CHAR ( date_time, 'YYMMDDHH24'))
     23* ORDER BY 1
    SQL> /
    
    GROUP_NUMBER         FROM_DATE                     TO_DATE                           COUNT#
    -------------------- ----------------------------- ----------------------------- ----------
    Group #1 =           19-Oct-1994 08:00:22          19-Oct-1994 08:41:22                   5
    Group #2 =           19-Oct-1994 11:35:47          19-Oct-1994 11:35:47                   5
    Group #3 =           19-Oct-1994 15:13:46          19-Oct-1994 15:13:46                   5
    How do I return the count per group, and take into consideration the dates and times change?
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2013
    Posts
    5
    Rep Power
    0
    I got your statement to work, BUT, what if a set go's over the hour mark? Say the last record in my example was 9:01, this still belongs to the first group, but your statement would put it in it's own group. The groups should be records with less then one hour difference between them. PLEASE HELP! I really need this.

IMN logo majestic logo threadwatch logo seochat tools logo