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?

Answer 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