OTBI - Defaulting Date Values
Summary
OTBI - Default Date ValuesContent
Requirement
Let say, your financial year start date is 01-Jul.
If the month is between 01-Jul(previous year Eg., 2020) to 30-Jun, then date should be defaulted to 01-Jul-2020
If the month is between 01-Jul(current year Eg., 2021) to 30-Jun, then date should be defaulted to 01-Jul-2021
The below query will help you to set the default values with the above requirement,
SELECT CASE WHEN MONTH("Workforce Management - Worker Assignment Event Real Time"."Time"."Calendar Date")<7 AND "Workforce Management - Worker Assignment Event Real Time"."Time"."Calendar Year" =YEAR("Workforce Management - Worker Assignment Event Real Time"."Time"."Calendar Date") THEN TIMESTAMPADD(SQL_TSI_MONTH,-6,"Workforce Management - Worker Assignment Event Real Time"."Time"."Calendar Year Start Date") WHEN MONTH("Workforce Management - Worker Assignment Event Real Time"."Time"."Calendar Date")>=7 AND "Workforce Management - Worker Assignment Event Real Time"."Time"."Calendar Year" =YEAR("Workforce Management - Worker Assignment Event Real Time"."Time"."Calendar Date") THEN TIMESTAMPADD(SQL_TSI_MONTH,6,"Workforce Management - Worker Assignment Event Real Time"."Time"."Calendar Year Start Date") END s_1 FROM "Workforce Management - Worker Assignment Event Real Time" WHERE ("Time"."Calendar Date" = CURRENT_DATE)