Oracle Transactional Business Intelligence

Home› Oracle Analytics› Oracle Analytics Forums› More Solutions› Oracle Transactional Business Intelligence

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Formula to Calculate Between Two Dates

Accepted answer
628
Views
3
Comments
Ashley Dore
Ashley Dore Rank 1 - Community Starter
in Oracle Transactional Business Intelligence

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)?



image.png
image.png
image.png


Tagged:

Best Answer

  • Nathan CCC
    Nathan CCC Rank 7 - Analytics Coach
    edited February 28 Answer ✓

    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


    image.png 
    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

Answers

  • melek cinar
    melek cinar Rank 1 - Community Starter
    edited February 28

    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
    Ashley Dore Rank 1 - Community Starter

    This worked. Thank you for your assistance!