L Kark Posted October 11, 2018 Share Posted October 11, 2018 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 days06-MAR-1406-MAR-14011-MAR-1411-MAR-14002-APR-1402-APR-14029-APR-1429-APR-14019-MAY-1410-JUN-141619-MAY-1413-JUN-141910-JUN-1410-JUN-14013-JUN-1413-JUN-14013-JUN-1413-JUN-14002-JUL-1402-JUL-14016-JUL-1416-JUL-14022-JUL-1422-JUL-14011-AUG-1411-AUG-14013-AUG-1413-AUG-14013-AUG-1413-AUG-14013-AUG-1413-AUG-140 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 More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now