Quick Report - data between two parameter dates

Hi @Rusiru 

It works now 

Is it possible to add comments by default when we press View Report?

I want to provide date format information.

Thanks,

Narsi.

You add comments in the report definition, like below.

Thank you so much @Rusiru and @anmise 

Thanks @Jur 

trunc(REAL_FINISHED) between NVL(&ABSENCE_PERIOD.DATE_FROM,TRUNC(sysdate)) and  nvl(NVL(&ABSENCE_PERIOD.DATE_TO,&ABSENCE_PERIOD.DATE_FROM),trunc(sysdate))

If you're going to nest NVL functions, it might be better to use COALESCE. (As a bonus, COALESCE is ANSI SQL and not Oracle-specific, so developers coming from other relational database systems may understand it.)

TRUNC(real_finished) BETWEEN COALESCE(&absence_period.date_from,
                                      TRUNC(SYSDATE))
                         AND COALESCE(&absence_period.date_to,
                                      &absence_period.date_from,
                                      TRUNC(SYSDATE))

By applying a function to the view or table column before it’s compared to the user value, it forces the system to run a full scan and compute that function on every record. The database isn’t smart enough to work backwards, predict what the outcome of that function should be, and find records that satisfy the output condition. This means this query won’t take advantage of indexes if you have any (notwithstanding function indexes which aren’t often used).

If you happen to have an index on REAL_FINISHED, this next iteration of the predicate will take advantage of it. (Pick the variant you find more readable.)

real_finished BETWEEN COALESCE(&absence_period.date_from,
                               TRUNC(SYSDATE))
                  AND COALESCE(&absence_period.date_to,
                               &absence_period.date_from,
                               TRUNC(SYSDATE)) + (1 - (1 / (60 * 60 * 24)))

real_finished BETWEEN COALESCE(&absence_period.date_from,
                               TRUNC(SYSDATE))
                  AND COALESCE(&absence_period.date_to,
                               &absence_period.date_from,
                               TRUNC(SYSDATE)) + (86399 / 86400)

If you know your REAL_FINISHED is always truncated back to midnight, then that would make your query even simpler. I’m not familiar with this view, but you could look in your data to discover that.

SELECT *
  FROM some_view
 WHERE real_finished != TRUNC(real_finished);