Oracle Fusion Data Intelligence

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Fusion Data Intelligence› Oracle Fusion Data Intelligence

Categories

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

TimeStampDiff in Hours Excluding Weekends

Received Response
26
Views
4
Comments
JobDeLuna
JobDeLuna Rank 5 - Community Champion
in Oracle Fusion Data Intelligence

Hi,

Anyone can help how can I get the timestampdiff in hours between two dates:

  1. ("Procurement - Purchase Orders"."Requisition Lines"."DW_FA_X_REQ_LINE_TIMESTAMPS_LASTAPPROVALDATE")
  2. ("Procurement - Purchase Orders"."Document Details"."DW_FA_X_PO_TIMESTAMPS_CREATIONDATE")

and weekend should be excluded in the calculation?

I have this below that shows timestampdiff in days, however I want to see the result in hours.

Thanks!

CASE WHEN DAYOFWEEK("Procurement - Purchase Orders"."Requisition Lines"."DW_FA_X_REQ_LINE_TIMESTAMPS_LASTAPPROVALDATE") > DAYOFWEEK("Procurement - Purchase Orders"."Document Details"."DW_FA_X_PO_TIMESTAMPS_CREATIONDATE") THEN CASE WHEN DAYOFWEEK("Procurement - Purchase Orders"."Requisition Lines"."DW_FA_X_REQ_LINE_TIMESTAMPS_LASTAPPROVALDATE") = 7 THEN TIMESTAMPDIFF(SQL_TSI_DAY, "Procurement - Purchase Orders"."Requisition Lines"."DW_FA_X_REQ_LINE_TIMESTAMPS_LASTAPPROVALDATE", "Procurement - Purchase Orders"."Document Details"."DW_FA_X_PO_TIMESTAMPS_CREATIONDATE") - (Floor(TIMESTAMPDIFF(SQL_TSI_DAY, "Procurement - Purchase Orders"."Requisition Lines"."DW_FA_X_REQ_LINE_TIMESTAMPS_LASTAPPROVALDATE", "Procurement - Purchase Orders"."Document Details"."DW_FA_X_PO_TIMESTAMPS_CREATIONDATE")/7)*2)-1 ELSE TIMESTAMPDIFF(SQL_TSI_DAY, "Procurement - Purchase Orders"."Requisition Lines"."DW_FA_X_REQ_LINE_TIMESTAMPS_LASTAPPROVALDATE", "Procurement - Purchase Orders"."Document Details"."DW_FA_X_PO_TIMESTAMPS_CREATIONDATE") - (Floor(TIMESTAMPDIFF(SQL_TSI_DAY, "Procurement - Purchase Orders"."Requisition Lines"."DW_FA_X_REQ_LINE_TIMESTAMPS_LASTAPPROVALDATE", "Procurement - Purchase Orders"."Document Details"."DW_FA_X_PO_TIMESTAMPS_CREATIONDATE")/7)*2)-2 END ELSE TIMESTAMPDIFF(SQL_TSI_DAY, "Procurement - Purchase Orders"."Requisition Lines"."DW_FA_X_REQ_LINE_TIMESTAMPS_LASTAPPROVALDATE", "Procurement - Purchase Orders"."Document Details"."DW_FA_X_PO_TIMESTAMPS_CREATIONDATE") - (Floor(TIMESTAMPDIFF(SQL_TSI_DAY, "Procurement - Purchase Orders"."Requisition Lines"."DW_FA_X_REQ_LINE_TIMESTAMPS_LASTAPPROVALDATE", "Procurement - Purchase Orders"."Document Details"."DW_FA_X_PO_TIMESTAMPS_CREATIONDATE")/7)*2) END

Answers

  • Sumanth V -Oracle
    Sumanth V -Oracle Rank 8 - Analytics Strategist

    @JobDeLuna - Please try using the below query and let us know if it helps:

    CASE
    WHEN DAYOFWEEK("Procurement - Purchase Orders"."Requisition Lines"."DW_FA_X_REQ_LINE_TIMESTAMPS_LASTAPPROVALDATE") > DAYOFWEEK("Procurement - Purchase Orders"."Document Details"."DW_FA_X_PO_TIMESTAMPS_CREATIONDATE")
    THEN
    CASE
    WHEN DAYOFWEEK("Procurement - Purchase Orders"."Requisition Lines"."DW_FA_X_REQ_LINE_TIMESTAMPS_LASTAPPROVALDATE") = 7
    THEN
    TIMESTAMPDIFF(SQL_TSI_HOUR, "Procurement - Purchase Orders"."Requisition Lines"."DW_FA_X_REQ_LINE_TIMESTAMPS_LASTAPPROVALDATE", "Procurement - Purchase Orders"."Document Details"."DW_FA_X_PO_TIMESTAMPS_CREATIONDATE")
    - (FLOOR(TIMESTAMPDIFF(SQL_TSI_HOUR, "Procurement - Purchase Orders"."Requisition Lines"."DW_FA_X_REQ_LINE_TIMESTAMPS_LASTAPPROVALDATE", "Procurement - Purchase Orders"."Document Details"."DW_FA_X_PO_TIMESTAMPS_CREATIONDATE") / 168) * 48) - 1
    ELSE
    TIMESTAMPDIFF(SQL_TSI_HOUR, "Procurement - Purchase Orders"."Requisition Lines"."DW_FA_X_REQ_LINE_TIMESTAMPS_LASTAPPROVALDATE", "Procurement - Purchase Orders"."Document Details"."DW_FA_X_PO_TIMESTAMPS_CREATIONDATE")
    - (FLOOR(TIMESTAMPDIFF(SQL_TSI_HOUR, "Procurement - Purchase Orders"."Requisition Lines"."DW_FA_X_REQ_LINE_TIMESTAMPS_LASTAPPROVALDATE", "Procurement - Purchase Orders"."Document Details"."DW_FA_X_PO_TIMESTAMPS_CREATIONDATE") / 168) * 48) - 2
    END
    ELSE
    TIMESTAMPDIFF(SQL_TSI_HOUR, "Procurement - Purchase Orders"."Requisition Lines"."DW_FA_X_REQ_LINE_TIMESTAMPS_LASTAPPROVALDATE", "Procurement - Purchase Orders"."Document Details"."DW_FA_X_PO_TIMESTAMPS_CREATIONDATE")
    - (FLOOR(TIMESTAMPDIFF(SQL_TSI_HOUR, "Procurement - Purchase Orders"."Requisition Lines"."DW_FA_X_REQ_LINE_TIMESTAMPS_LASTAPPROVALDATE", "Procurement - Purchase Orders"."Document Details"."DW_FA_X_PO_TIMESTAMPS_CREATIONDATE") / 168) * 48)
    END

    This query should now return the time difference in hours while considering weekends.

  • JobDeLuna
    JobDeLuna Rank 5 - Community Champion

    Hi, @Sumanth V -Oracle , thank you for your response, however my goal is not to consider the weekends or exclude the weekends, specifically Saturday and Sunday. Thank you!

  • JobDeLuna
    JobDeLuna Rank 5 - Community Champion

    Hi, @Sumanth V -Oracle , I have tried to use the calculation that you sent however it seems it still considers the weekend

    image.png

    and it should 24 hours only

  • JobDeLuna
    JobDeLuna Rank 5 - Community Champion

    I have this bucketing of PR to PO Cycle time and currently this considers the weekends and my goal is to exclude the weekends in the calculation:

    Case when ((TIMESTAMPDIFF(SQL_TSI_HOUR,"Procurement - Purchase Orders"."Requisition Lines"."DW_FA_X_REQ_LINE_TIMESTAMPS_LASTAPPROVALDATE","Procurement - Purchase Orders"."Document Details"."DW_FA_X_PO_TIMESTAMPS_CREATIONDATE"))) <= 8 THEN '.WITHIN 8 HOURS' WHEN ((TIMESTAMPDIFF(SQL_TSI_HOUR,"Procurement - Purchase Orders"."Requisition Lines"."DW_FA_X_REQ_LINE_TIMESTAMPS_LASTAPPROVALDATE","Procurement - Purchase Orders"."Document Details"."DW_FA_X_PO_TIMESTAMPS_CREATIONDATE"))) > 8 AND ((TIMESTAMPDIFF(SQL_TSI_HOUR,"Procurement - Purchase Orders"."Requisition Lines"."DW_FA_X_REQ_LINE_TIMESTAMPS_LASTAPPROVALDATE","Procurement - Purchase Orders"."Document Details"."DW_FA_X_PO_TIMESTAMPS_CREATIONDATE"))) <= 24 THEN '>08 HRS to 1 DAY' WHEN ((TIMESTAMPDIFF(SQL_TSI_HOUR,"Procurement - Purchase Orders"."Requisition Lines"."DW_FA_X_REQ_LINE_TIMESTAMPS_LASTAPPROVALDATE","Procurement - Purchase Orders"."Document Details"."DW_FA_X_PO_TIMESTAMPS_CREATIONDATE"))) > 24 AND ((TIMESTAMPDIFF(SQL_TSI_HOUR,"Procurement - Purchase Orders"."Requisition Lines"."DW_FA_X_REQ_LINE_TIMESTAMPS_LASTAPPROVALDATE","Procurement - Purchase Orders"."Document Details"."DW_FA_X_PO_TIMESTAMPS_CREATIONDATE"))) <= 48 THEN '>1 DAY to 2 DAYS' WHEN
    ((TIMESTAMPDIFF(SQL_TSI_HOUR,"Procurement - Purchase Orders"."Requisition Lines"."DW_FA_X_REQ_LINE_TIMESTAMPS_LASTAPPROVALDATE","Procurement - Purchase Orders"."Document Details"."DW_FA_X_PO_TIMESTAMPS_CREATIONDATE"))) > 48 AND ((TIMESTAMPDIFF(SQL_TSI_HOUR,"Procurement - Purchase Orders"."Requisition Lines"."DW_FA_X_REQ_LINE_TIMESTAMPS_LASTAPPROVALDATE","Procurement - Purchase Orders"."Document Details"."DW_FA_X_PO_TIMESTAMPS_CREATIONDATE"))) <= 72 THEN '>2 DAYS to 3 DAYS' WHEN
    ((TIMESTAMPDIFF(SQL_TSI_HOUR,"Procurement - Purchase Orders"."Requisition Lines"."DW_FA_X_REQ_LINE_TIMESTAMPS_LASTAPPROVALDATE","Procurement - Purchase Orders"."Document Details"."DW_FA_X_PO_TIMESTAMPS_CREATIONDATE"))) > 72 AND ((TIMESTAMPDIFF(SQL_TSI_HOUR,"Procurement - Purchase Orders"."Requisition Lines"."DW_FA_X_REQ_LINE_TIMESTAMPS_LASTAPPROVALDATE","Procurement - Purchase Orders"."Document Details"."DW_FA_X_PO_TIMESTAMPS_CREATIONDATE"))) <= 96 THEN '>3 DAYS to 4 DAYS' WHEN
    ((TIMESTAMPDIFF(SQL_TSI_HOUR,"Procurement - Purchase Orders"."Requisition Lines"."DW_FA_X_REQ_LINE_TIMESTAMPS_LASTAPPROVALDATE","Procurement - Purchase Orders"."Document Details"."DW_FA_X_PO_TIMESTAMPS_CREATIONDATE"))) > 96 AND ((TIMESTAMPDIFF(SQL_TSI_HOUR,"Procurement - Purchase Orders"."Requisition Lines"."DW_FA_X_REQ_LINE_TIMESTAMPS_LASTAPPROVALDATE","Procurement - Purchase Orders"."Document Details"."DW_FA_X_PO_TIMESTAMPS_CREATIONDATE"))) <= 120 THEN '>4 DAYS to 5 DAYS' WHEN ((TIMESTAMPDIFF(SQL_TSI_HOUR,"Procurement - Purchase Orders"."Requisition Lines"."DW_FA_X_REQ_LINE_TIMESTAMPS_LASTAPPROVALDATE","Procurement - Purchase Orders"."Document Details"."DW_FA_X_PO_TIMESTAMPS_CREATIONDATE"))) > 120 THEN '>5 DAYS' ELSE 'NULL' END