CODEWITHSUNDEEP
sqloracle-databasedatesumperiod
gbvisconti
gbvisconti

Reputation: 412

Sum days by period with sql- Oracle

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?

Upvotes: 0

Views: 1263

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

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.

Upvotes: 1

Mikhail Timofeev
Mikhail Timofeev

Reputation: 2169

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

Upvotes: 1

Related Questions