Sum days by period with sql- Oracle

Question

I have a table to register which departments a person has worked and the date when it begin and ended.

If I define a period, how can I get for each person the number of days that he/she worked?

For example.

When the period is between 01/05/2014 and 31/07/2014 (date format is dd/mm/yyyy)

(id_department, id_person, date_start, date_end)

Person #856

190 856 04/07/2014  null
12  856 17/05/2004  13/06/2008
6   856 13/06/2008  19/09/2013
169 856 19/09/2013  03/07/2014

Should return number of days worked: 90 days (63 from 01/05/2014 to 07/07/2014 plus 27 from 04/07/2014 to 31/07/2014)

Person #900

19 900  30/07/2014  null
days: 1

Person #800

21 800  19/02/2013  05/06/2014
days: 35

Person #100

21 100  24/03/2012  05/05/2014
days: 4

and any combination of dates possible. Is it possible to get the sum in one query?

Assume this is for Oracle because that is in the title. – Gordon Linoff Sep 10 '14 at 13:03 — Gordon Linoff, Sep 10 '14 at 13:03

Mikhail Timofeev · Accepted Answer · 2014-09-10T13:19:40.330

1

something like this should work:

select person_id, 
SUM(case 
      when 
       date_start >= to_date('01/05/2014','dd/mm/yyyy') 
       and date_end <= to_date('31/07/2014', 'dd/mm/yyyy') 
      then (date_end - date_start)
      when
       to_date('01/05/2014','dd/mm/yyyy') > date_start 
       and to_date('31/07/2014', 'dd/mm/yyyy') < date_end
      then (to_date('31/07/2014','dd/mm/yyyy') - to_date('01/05/2014','dd/mm/yyyy')) 
      when
       to_date('01/05/2014','dd/mm/yyyy') < date_start 
       and to_date('31/07/2014', 'dd/mm/yyyy') < date_end
      then (to_date('31/07/2014','dd/mm/yyyy') - date_start)  
      when
       to_date('01/05/2014','dd/mm/yyyy') > date_start 
       and to_date('31/07/2014', 'dd/mm/yyyy') > date_end
      then (date_end - to_date('01/05/2014','dd/mm/yyyy'))  
      else 0
    end)
as days
from table_name 
group by person_id

edited Sep 10 '14 at 13:19

answered Sep 10 '14 at 12:24

Mikhail Timofeev

2,169
15
13

1

The problem here is that if a person started before 01/05/2014 and ended after 31/07/2014 the clauses will eliminate the sum, which should be 90 days – gbvisconti Sep 10 '14 at 12:28
i edited the answer, i hope oracle understands `case when` in `sum()`. – Mikhail Timofeev Sep 10 '14 at 12:38
I does, but most of rows are returning zero. I'm trying to change the query a little, it will help a lot – gbvisconti Sep 10 '14 at 12:49
There are four conditions. Let A = '01/05/2014' and Z = '31/07/2014'. 1) When start_date > A and end_date < Z. 2) When start_date < A and end_date > Z. 3) When start_date > A and end_date > Z. 4) When start_date < A and end_date < Z. Your update code only handles cases 1 and 2, not the other two cases. – CodeNewbie Sep 10 '14 at 12:57
@CodeNewbie thank you, it's right. Edited one more time. – Mikhail Timofeev Sep 10 '14 at 13:20
+1. It's correct. Although I prefer Gordon Linoff's method. That approach is much better and it is what I was trying to think too. – CodeNewbie Sep 10 '14 at 13:24
I believe the query is correct and consider all cases. I'm trying to understand why some days are coming like 1 -12036 2 -9435 3 -7414 Maybe I have a problem with date format – gbvisconti Sep 10 '14 at 14:38

Gordon Linoff · Answer 2 · 2014-09-10T13:30:22.090

1

Here is a pretty simple method:

with dates as (
      select date '2014-05-01' as PeriodStart, date '2014-07-31' as PeriodEnd
      from dual
     )
select p.*,
       (case when p.date_end >= dates.PeriodStart and p.date_start <= dates.PeriodEnd
             then least(dates.PeriodEnd, p.date_end) - greatest(dates.PeriodStart, p.date_start)
        end) as DaysInPeriod
from dates cross join
     persons p;

Note this uses Oracle's standard for a date constant (the date keyword followed by the ISO standard YYYY-MM-DD format). You can output dates in whatever format you like, but when you work with them in the database, you should use the native formats.

edited Sep 10 '14 at 13:30

answered Sep 10 '14 at 13:09

Gordon Linoff

1,242,037
58
646
786

I think you should change the condition to `when p.date_end >= dates.PeriodStart or p.date_start <= p.PeriodEnd`. Because AND will take into account only those cases where both start date and end date are between the specified period. – CodeNewbie Sep 10 '14 at 13:15
@CodeNewbie . . . No, the `and` is correct (the alias on `PeriodEnd` was wrong, but that is a different matter). Two periods overlap when one starts before the second ends and the first ends after the second starts. – Gordon Linoff Sep 10 '14 at 13:31
you're right. My apologies. I didn't see you compared p.date_end with dates.PeriodStart and vice versa. Your answer is good. – CodeNewbie Sep 10 '14 at 13:40

Sum days by period with sql- Oracle

2 Answers2