- If the month is between 01-Jul(previous year Eg., 2020) to 30-Jun, then the date should default to 01-Jul-2020.
- If the month is between 01-Jul(current year Eg., 2021) to 30-Jun, then the date should default to 01-Jul-2021
Solution:
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)
