Overlapping Date Ranges

0

Hi. This may be more of an Oracle/SQL question, but perhaps someone has experience with this and can help me out. In the query I am using to develop my Jasper report, I am using a table in which each record has a start date and end date. I need to total the number of days between each start and end date for each record and then add all those numbers up to get a total amount of days for the entire table. The problem is that some of the date ranges in the different records overlap eachother, and I need to be able to take those overlapping days into account so they are not counted twice in the final total number of days I need to calculate for the entire table. See sample below.

Any tips or advice?

Thank you!

For example, here is a sample table:

Activity Start Date Activity End Date # of days

06-MAR-14

06-MAR-14

0

11-MAR-14

11-MAR-14

0

02-APR-14

02-APR-14

0

29-APR-14

29-APR-14

0

19-MAY-14

10-JUN-14

16

19-MAY-14

13-JUN-14

19

10-JUN-14

10-JUN-14

0

13-JUN-14

13-JUN-14

0

13-JUN-14

13-JUN-14

0

02-JUL-14

02-JUL-14

0

16-JUL-14

16-JUL-14

0

22-JUL-14

22-JUL-14

0

11-AUG-14

11-AUG-14

0

13-AUG-14

13-AUG-14

0

13-AUG-14

13-AUG-14

0

13-AUG-14

13-AUG-14

0
  TOTAL 35 days (but this is wrong - I want the total to really be 19 days because there are overlapping days between the two date ranges that actually spanned more than a single day)
L Kark's picture
4
Joined: Jul 17 2015 - 7:19am
Last seen: 1 year 2 days ago

0 Answers:

No answers yet
Feedback