Oracle Transactional Business Intelligence

Formula to Calculate Between Two Dates

Accepted answer
583
Views
3
Comments

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




Tagged:

Best Answer

  • Nathan CCC
    Nathan CCC ✭✭✭✭✭
    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


    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
    edited February 28

    Hi @Ashley Dore, @Nathan CCC

    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

  • This worked. Thank you for your assistance!