L Kark

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-14 06-MAR-14 011-MAR-14 11-MAR-14 002-APR-14 02-APR-14 029-APR-14 29-APR-14 019-MAY-14 10-JUN-14 1619-MAY-14 13-JUN-14 1910-JUN-14 10-JUN-14 013-JUN-14 13-JUN-14 013-JUN-14 13-JUN-14 002-JUL-14 02-JUL-14 016-JUL-14 16-JUL-14 022-JUL-14 22-JUL-14 011-AUG-14 11-AUG-14 013-AUG-14 13-AUG-14 013-AUG-14 13-AUG-14 013-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)

Hello. I am trying to create a field in my report which counts the amount of days between two dates excluding any weekends. Would someone have experience with this who can guide me as to the best way to go about this? Perhaps, the best would be to do the calculation in the query itself. If so, how would I figure out which days are weekends in the date range, so I can subtract those days from my final count of days between the start and end date. I am querying using Oracle DB. Thank you!

@reportdev - This was very helpful and helped me accomplish what I needed. Thank you!

Hello. I have a report which I am designing in TIBCO Jaspersoft Studio and then publishing to the Jasper Server. I have a query based multi select input parameter in this report (parameter type: list). I would like to be able to display the word "All" on the report if the user chooses to 'select all' in the input parameter. Is there a way to determine if the user selected all available options in the multi select parameter? Thank you.