Thread: Sum Interval Dates

1. Registered User
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. Registered User
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. Registered User
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. Registered User
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. Registered User
Join Date
Oct 2014
Posts
3
Thank You A LOT! !!! I will test here and give you a feedback!!

6. Registered User
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
•