nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

Formula to Calculate Between Two Dates

Ashley Dore

I've added a column to my OTBI analysis to calculate the days between when a Requisition is approved to when the corresponding Purchase Order is approved. Since the date format for Requisition Approval Date and Purchase Order Approval Date have the date and time, my calculation has rounding differences that I've tried to correct for. However, this did not solve for each scenario in my analysis and there are still 100+ lines that are not calculating properly. Is there a way to exclude the date from the dates in the calculation so the calculation is only based on days (not days and time)?

Find more posts tagged with

Procurement

OTBI

Accepted answers

Nathan CCC

Hi Ashley,

Consider using instead the function that is designed for this purpose. TIMESTAMPDIFF()

This will give you the number of seconds or mins or hours or days etc. rounded to the nearest integer between 2 timestamp attribute columns. So if you use sql_tsi_day between 2 datetimes then you will get to the nearest whole number of days.

For more information read your user guide

Oracle® Fusion Middleware

Logical SQL Reference Guide for Oracle Business Intelligence Enterprise Edition

Release 12c (12.2.1.3.0)

E80604-01

August 2017

https://docs.oracle.com/middleware/12213/biee/BIESQ/toc.htm#GUID-2DBD6310-9D95-4D39-99A9-5B29A4160002

For example

select all 0 s_0
, cast("Purchase Order"."PO Header Id" as character) as po_id
, "Purchase Order"."Order" as po
, "Requisition"."Requisition Number" as pr
, "Purchase Order Additional Attributes"."Approval Date" as po_approved
, "Requisition"."Requisition Approved Date" as pr_approved
, timestampdiff(sql_tsi_day
    ,"Purchase Order Additional Attributes"."Approval Date"
    ,"Requisition"."Requisition Approved Date"
    ) as n
from "Procurement - Procure To Pay Real Time"
where 1 = 1
and (
("Purchase Order Additional Attributes"."Approval Date" is not null)
or ("Requisition"."Requisition Approved Date" is not null)
)
order by 7 asc nulls last, 1 asc nulls last, 2 asc nulls last, 3 asc nulls last, 4 asc nulls last
FETCH FIRST 7 ROWS ONLY

All comments

Nathan CCC

Hi Ashley,

Consider using instead the function that is designed for this purpose. TIMESTAMPDIFF()

For more information read your user guide

Oracle® Fusion Middleware

Logical SQL Reference Guide for Oracle Business Intelligence Enterprise Edition

Release 12c (12.2.1.3.0)

E80604-01

August 2017

https://docs.oracle.com/middleware/12213/biee/BIESQ/toc.htm#GUID-2DBD6310-9D95-4D39-99A9-5B29A4160002

For example

select all 0 s_0
, cast("Purchase Order"."PO Header Id" as character) as po_id
, "Purchase Order"."Order" as po
, "Requisition"."Requisition Number" as pr
, "Purchase Order Additional Attributes"."Approval Date" as po_approved
, "Requisition"."Requisition Approved Date" as pr_approved
, timestampdiff(sql_tsi_day
    ,"Purchase Order Additional Attributes"."Approval Date"
    ,"Requisition"."Requisition Approved Date"
    ) as n
from "Procurement - Procure To Pay Real Time"
where 1 = 1
and (
("Purchase Order Additional Attributes"."Approval Date" is not null)
or ("Requisition"."Requisition Approved Date" is not null)
)
order by 7 asc nulls last, 1 asc nulls last, 2 asc nulls last, 3 asc nulls last, 4 asc nulls last
FETCH FIRST 7 ROWS ONLY

Melek Cinar

Hi @Ashley Dore, @Nathan CCC

Resource Assignment Cost Split Report.docx

We have resource assignment report and our aim is to join Project Resource Assignment and Worker Assignment, in order to calculate each employee ‘s project assignment percentages each month and split payroll cost to projects accordingly. We have included the XML and SQL codes of the report in the document, along with detailing how we want the computation to be. Your suggestion would be greatly appreciated and very helpful.

Thank you.

Regards,

Melek

Ashley Dore

This worked. Thank you for your assistance!