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

How to find if a specific day falls between two dates - OTBI

Accepted answer
54
Views
3
Comments
Andrew Mc
Andrew Mc Rank 3 - Community Apprentice
in Oracle Transactional Business Intelligence

Trying to create a formula to show yes/no if the 12th of the month falls between {"Payroll Period"."Start Date"} and {"Payroll Period"."End Date"}

I attempted a simple formula:

CASE WHEN DAY("Payroll Period"."Start Date") <= 12 and DAY("Payroll Period"."End Date") >=12 THEN 'Yes' WHEN DAY("Payroll Period"."End Date") = 12 THEN 'Yes' END

But pay periods - 12/31/2023 - 01/13/2024 and 06/30/2024 - 07/13/2024 are being excluded from that logic.

Thank you,

Andrew

Tagged:

Best Answer

  • Nathan CCC
    Nathan CCC Rank 7 - Analytics Coach
    Answer ✓

    So for a day of the month n=12 to be in a date range from start date YYYY-mm-dd to end date YYYY-mm-dd then assuming end date > = start date then i think you need to test how many months between the start end dates. To include day 12; If same year month difference in months = 0 then start date must be > = 12 AND end date must be > = 12 . If consecutive months difference in months = 1 then start date must be > = 12 OR end date must be < = 12. If difference in months = 2+ then the data range will always include day 1-28 including day 12.

Answers

  • Nathan CCC
    Nathan CCC Rank 7 - Analytics Coach
    edited Apr 15, 2024 4:06PM

    Hi,

    For test case 1 payroll day of start date 2023-12-31 = 31 and day of end date 2024-01-13 is 13. 31 is NOT "< = 12", 13 is NOT "> = 12", 13 is not "= 12" - so it is not true for any of your 3 conditions in your switch so it is nether Yes nor Yes - the case will return null.

    For test case 2 payroll day of start date 2024-06-30 = 30 and day of end date 2024-07-13 is 13. 30 is NOT "lt = 12", 13 is NOT "gt = 12", 13 is not "= 12" - so it is not true for any of your 3 conditions in your switch so it is nether Yes nor Yes - the case will return null.

  • Andrew Mc
    Andrew Mc Rank 3 - Community Apprentice

    Thank you so much Nathan, that looks like it worked for me:

    CASE WHEN ((YEAR("Payroll Period"."End Date") - YEAR("Payroll Period"."Start Date")) * 12 + (MONTH("Payroll Period"."End Date") - MONTH("Payroll Period"."Start Date")) = 0 ) AND (DAY("Payroll Period"."Start Date") <= 12 AND DAY("Payroll Period"."End Date") >= 12) THEN 'Yes' WHEN ((YEAR("Payroll Period"."End Date") - YEAR("Payroll Period"."Start Date")) * 12 + (MONTH("Payroll Period"."End Date") - MONTH("Payroll Period"."Start Date")) = 1 ) AND (DAY("Payroll Period"."Start Date") >= 12 AND DAY("Payroll Period"."End Date") >= 12) THEN 'Yes' END

    Capture04152024.PNG

    I checked back a couple of years and it appears to be working and it shows as 'Yes' with dates that have the 12th day in the range.

    Appreciate the help.