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) |
