Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2014
    Posts
    3

    Unanswered: Sum Interval Dates

    Hello Guys today I came across the following problem:

    I would like to sum the hours of each name, giving a total interval between START and END activities,
    would be simple if I could subtract from each record the end of the beginning, more eg Mary, started 13th and was up to 15 and started another activity while 14 and 16, I would like the result of it was 3 (she used 3 hours of their time to perform both activities)

    eg.
    Name | START | END |
    ----------------------------------------------------------
    KATE | 2014-01-01 13:00:00 | 2014-01-01 14:00:00 |
    MARY | 2014-01-01 13:00:00 | 2014-01-01 15:00:00 |
    TOM | 2014-01-01 13:00:00 | 2014-01-01 16:00:00 |
    KATE | 2014-01-01 12:00:00 | 2014-01-02 04:00:00 |
    MARY | 2014-01-01 14:00:00 | 2014-01-01 16:00:00 |
    TOM | 2014-01-01 12:00:00 | 2014-01-01 18:00:00 |
    TOM | 2014-01-01 22:00:00 | 2014-01-02 02:00:00 |
    result

    KATE 15 hours
    MARY 3 hours
    TOM 9 hours

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Aren't the results like followings?

    KATE 16 hours
    TOM 10 hours

    e.g.
    KATE | 2014-01-01 12:00:00 | 2014-01-02 04:00:00 |
    KATE | 2014-01-01 13:00:00 | 2014-01-01 14:00:00 |
    Kate started 12 and ended next day's 04.

  3. #3
    Join Date
    Oct 2014
    Posts
    3
    Tonkuma, you are right about Kate & Tom, do you have a solution?

    can be in SQL, stored procedure or function

    thanks

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Here is an example written for DB2, and tested on DB2 9.7.5 for Windows.

    Though it must be necessary to revise the query to conform with MySQL syntax,
    I hope the example gave you some help.

    Code:
    WITH
     activities
    ( person_name , start_time , end_time ) AS (
    VALUES
      ( 'KATE' , '2014-01-01 13:00:00' , '2014-01-01 14:00:00' )
    , ( 'MARY' , '2014-01-01 13:00:00' , '2014-01-01 15:00:00' )
    , ( 'TOM'  , '2014-01-01 13:00:00' , '2014-01-01 16:00:00' )
    , ( 'KATE' , '2014-01-01 12:00:00' , '2014-01-02 04:00:00' )
    , ( 'MARY' , '2014-01-01 14:00:00' , '2014-01-01 16:00:00' )
    , ( 'TOM'  , '2014-01-01 12:00:00' , '2014-01-01 18:00:00' )
    , ( 'TOM'  , '2014-01-01 22:00:00' , '2014-01-02 02:00:00' )
    )
    , concat_periods
    ( k , n , person_name , start_time , end_time ) AS (
    SELECT 0
         , n
         , person_name , start_time , end_time
     FROM  (SELECT a.*
                 , ROW_NUMBER() OVER() AS n
             FROM  activities AS a
           ) AS a
     WHERE NOT EXISTS
           (SELECT 0
             FROM  activities AS e
             WHERE e.person_name = a.person_name
              AND  e.start_time <  a.start_time
              AND  e.end_time   >= a.start_time
           )
    UNION ALL
    SELECT k + 1
         , c.n
         , c.person_name
         , c.start_time
         , n.end_time
     FROM  concat_periods AS c /* current */
         , activities     AS n /* new or next */
     WHERE k < 1000
      AND  n.person_name = c.person_name
      AND  n.start_time <= c.end_time
      AND  n.end_time   >  c.end_time
    )
    SELECT person_name
         , SUM(  ( DAYS(end_time) - DAYS(start_time) ) * 24
               + ( HOUR(end_time) - HOUR(start_time) )
              ) AS used_hours
     FROM  concat_periods AS c
     WHERE k = (SELECT MAX(k)
                 FROM  concat_periods AS m
                 WHERE m.person_name = c.person_name
                  AND  m.n           = c.n
               )
     GROUP BY
           person_name
    ;
    The result was
    Code:
    PERSON_NAME USED_HOURS 
    ----------- -----------
    KATE                 16
    MARY                  3
    TOM                  10
    
      3 record(s) selected.
    Last edited by tonkuma; 10-22-14 at 14:28.

  5. #5
    Join Date
    Oct 2014
    Posts
    3

    Cool

    Thank You A LOT! !!! I will test here and give you a feedback!!

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    My first example was not work for more complex(many overlaps) data.

    For example: (schematized)
    Code:
     |     2014-01-01        |     2014-01-02        |
     0     6    12    18     0     6    12    18     0
     +-----+-----+-----+-----+-----+-----+-----+-----+
     Alice:
                 <------>
                       <------->
                    <--------->
                            <------>
                               <--->
                        <->
                                           <---->
                                              <--->
                                       <-->
    
     +-----+-----+-----+-----+-----+-----+-----+-----+
     0     6    12    18     0     6    12    18     0

    Note: Tested on DB2 9.7.5 for Window.

    Test data:
    Code:
    WITH
     activities
    ( person_name , start_time , end_time ) AS (
    VALUES
      ( 'KATE'   , '2014-01-01 13:00:00' , '2014-01-01 14:00:00' )
    , ( 'MARY'   , '2014-01-01 13:00:00' , '2014-01-01 15:00:00' )
    , ( 'TOM'    , '2014-01-01 13:00:00' , '2014-01-01 16:00:00' )
    , ( 'KATE'   , '2014-01-01 12:00:00' , '2014-01-02 04:00:00' )
    , ( 'MARY'   , '2014-01-01 14:00:00' , '2014-01-01 16:00:00' )
    , ( 'TOM'    , '2014-01-01 12:00:00' , '2014-01-01 18:00:00' )
    , ( 'TOM'    , '2014-01-01 22:00:00' , '2014-01-02 02:00:00' )
    , ( 'Alice'  , '2014-01-01 12:00:00' , '2014-01-01 19:00:00' )
    , ( 'Alice'  , '2014-01-01 18:00:00' , '2014-01-02 02:00:00' )
    , ( 'Alice'  , '2014-01-01 15:00:00' , '2014-01-02 01:00:00' )
    , ( 'Alice'  , '2014-01-01 23:00:00' , '2014-01-02 06:00:00' )
    , ( 'Alice'  , '2014-01-02 02:00:00' , '2014-01-02 06:00:00' )
    , ( 'Alice'  , '2014-01-01 19:00:00' , '2014-01-01 21:00:00' )
    , ( 'Alice'  , '2014-01-02 14:00:00' , '2014-01-02 19:00:00' )
    , ( 'Alice'  , '2014-01-02 17:00:00' , '2014-01-02 21:00:00' )
    , ( 'Alice'  , '2014-01-02 10:00:00' , '2014-01-02 13:00:00' )
    )

    Example 2: (coninued from test data)
    Code:
    /************************************************
    ** The column last_end_time in concat_periods  **
    **    was used to stop some useless recursion. **
    ** The query may work without the column.      **
    *************************************************/
    , concat_periods
    ( k , person_name , start_time , end_time , last_end_time ) AS (
    SELECT 0
         , person_name , start_time , end_time , start_time
     FROM  activities AS a
     WHERE NOT EXISTS
           (SELECT 0
             FROM  activities AS e
             WHERE e.person_name = a.person_name
              AND  e.start_time <  a.start_time
              AND  e.end_time   >= a.start_time
           )
    UNION ALL
    SELECT k + 1
         , c.person_name
         , c.start_time
         , n.end_time
         , c.end_time
     FROM  concat_periods AS c /* current */
         , activities     AS n /* new or next */
     WHERE k < 1000
      AND  n.person_name = c.person_name
      AND  n.start_time <= c.end_time
      AND  n.start_time >  c.last_end_time
      AND  n.end_time   >  c.end_time
    )
    SELECT person_name
         , SUM(  ( DAYS(end_time) - DAYS(start_time) ) * 24
               + ( HOUR(end_time) - HOUR(start_time) )
              ) AS used_hours
     FROM  (SELECT person_name
                 , start_time
                 , MAX(end_time) AS end_time
             FROM  concat_periods
             GROUP BY
                   person_name
                 , start_time
           )
     GROUP BY
           person_name
    ;

    The result was:
    Code:
    PERSON_NAME USED_HOURS 
    ----------- -----------
    Alice                28
    KATE                 16
    MARY                  3
    TOM                  10
    
      4 record(s) selected.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •