Sum the differences between dates

Question

I have 2 tables, master and detail, that both contain dates related to events. The first contain the master record's begin and end date. The second contains various gaps, that also have a beginning and an end, related to the master record and falling between its begin and end date. I can successfully calculate the total number of days between the master record's start and end but, yet I'm failing to see how i can aggregate the sum of the "off days" in the details table and group them by month. With this i mean:

Master table

Start date (MM/DD/YYYY): 01/01/2015
End date (MM/DD/YYYY): 01/25/2015

Total number of days: 25

Details table

Start date (MM/DD/YYYY) | End date (MM/DD/YYYY) : 
01/02/2015              | 01/05/2015 
01/09/2015              | 01/15/2015
01/18/2015              | 01/19/2015

Total number of "off days": 13

The DB environment is Oracle 11g. Can you help me?

Answer 1

Try this :

select sum(End_date-Start_date+1) from details_table; 

The sum function will sum the total of all the dates, which should give you the 13 "off days" you wanted.

If you want to add the start_date/end_date conditions, you can do it like this.

select sum(End_date-Start_date+1) from details_table 
where Start_date>=to_date('01/01/2015','mm/dd/yyyy') 
and End_date<=to_date('01/25/2015','mm/dd/yyyy');