Jump to content
Changes to the Jaspersoft community edition download ×

Overlapping Date Ranges


L Kark

Recommended Posts

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 DateActivity 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
 TOTAL35 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)
Link to comment
Share on other sites

  • Replies 0
  • Created
  • Last Reply

Top Posters In This Topic

Popular Days

Top Posters In This Topic

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...