Teradata Update based on Fiscal Month End

We need to determine if employee is New Hire or not. (Flag 'Y' or 'N'). A fiscal month starts on 22nd of each month and end on 21st of next month. Can you please help with the logic?

HireDateStart = AGENT_HIRE_DATE
HireDateStop = AGENT_HIRE_DATE + 60 days + the rest of the current fiscal month + an additional fiscal     period(30 days)
StartDate = FISCAL_MONTH start
StopDate = FISCAL_MONTH end

For example if hire date is 5/9/2022, they are considered New Hire until 8/21/2023. This is because the first 60 days end on 7/9/2023, but, since it lands in the middle of fiscal July, which will carry out until 7/21/2022, then the full fiscal period kicks in on 7/22/2022 and lasts until 8/21/2023.

Assumptions:

• IsNewHire field can be derived from AGENT_HIRE_DATE meeting the following criteria:
  • An employee is considered a new hire during the 60 days they are in training plus the full fiscal month following their new hire date
  • If the person's hire date is on the 15th of the month, that entire fiscal period would be set to "New Hire". If the 60 days cover the span of 3 fiscal months, all 3 fiscal months would show new hire

Data in ABC.DATE_DIM table:

DATE_DT       FISCAL_MONTH_END_DT   FISCAL_YEAR_NUM
7/23/2023   8/21/2023   2,023
7/22/2023   8/21/2023   2,023
7/21/2023   7/21/2023   2,023
7/20/2023   7/21/2023   2,023

I have tried something like below:

UPDATE TGT
FROM ABC.AGENT_DIM TGT,
(
SEL AD.AGENT_HIRE_DATE AS HireDateStart,
AD.AGENT_HIRE_DATE + INTERVAL '60' DAY AS HireDatePlus2Month,
DD.FISCAL_MONTH_END_DT AS HireDatePlus2Month_EndFiscal,
MonthEndFiscal + INTERVAL '30' DAY AS HireDateStop,
AD.NRDP_AGENT_ID,
cast(AD.RECORD_START_TS AS DATE)
FROM ABC.AGENT_DIM AD,ABC.DATE_DIM DD
WHERE (AD.AGENT_HIRE_DATE + INTERVAL '60' DAY) = DD.DATE_DT
and cast(AD.RECORD_START_TS AS DATE) <= HireDateStop
) src
SET 
ISNEWHIRE = 'Y'
WHERE TGT.NRDP_AGENT_ID = SRC.NRDP_AGENT_ID;