How to get total number of days report builder (oracle db)

Question

I have a report we have been using for quite some time for total inventory as of a specific date. However, I need to add a column that shows how many days this material has been in inventory. How do I accomplish this?

Let's say my query is:

MVT.INVT_LEV1,
MVT.INVT_LEV2,
MVT.INVT_LEV3,
MVT.INVT_LEV4,
INVT_ORG_RECD_DATE 
FROM M_INVT

INVT_ORG_RECD_DATE provides the date the material was received into our inventory. Let's say the data is 07/05/22. I want another column that says inventory has been with us for two days. How do I accomplish this? If this doesn't make sense I can copy over my entire query.

I would imagine the first date is the date in that column, and the second date would be the date I am running the report for? and some how need to system to figure out how many days in between?

Answer 1

The simplest option is to do the calculation within the query:

select invt_lev1, ...,
       --
       trunc(sysdate) - invt_org_recd_date as number_of_days
from m_invt

Why TRUNC? Because sysdate returns both date and time, so you'd get fractional number of days:

SQL> select sysdate as today,
  2         sysdate - date '2022-07-06' as diff_1,
  3         trunc(sysdate) - date '2022-07-06' diff_2
  4  from dual;

TODAY                   DIFF_1     DIFF_2
------------------- ---------- ----------
08.07.2022 06:59:51  2.2915625          2
                     ---------
                     without TRUNC
SQL>

---

Another option is to create a formula column in the Data Model Editor (click the appropriate button in the left-hand side toolbar and add it into the group that contains other fields). Name it e.g. CF_NUMBER_OF_DAYS and edit its properties; code you'd use would be

return trunc(sysdate) - :invt_org_recd_date;

as formula column represents a function that returns some value (a number, in your case).

---

Finally, add the new field into the Paper Layout Editor. I guess you know how to do that.