Categories
- All Categories
- 4 Oracle Analytics Videos
- 13.7K Oracle Analytics Forums
- 5.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 56 Oracle Analytics News
- 31 Oracle Analytics Trainings
- 52 Oracle Analytics Data Visualizations
- 2 Oracle Analytics Career
- 2 Oracle Analytics Industry
- Find Partners
- For Partners
Formula to Calculate Between Two Dates
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)?
Best 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
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
0
Answers
-
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
0 -
This worked. Thank you for your assistance!
0