0

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;
user7247147
  • 1,045
  • 1
  • 10
  • 24
Debasis
  • 21
  • 3
  • If you are updating TGT from SRC then the outer UPDATE should have a WHERE clause with (inner) join conditions. But SRC is just returning dates for all Agents so there is some logic missing. Do you need a CASE and/or a comparison to CURRENT_DATE or some other reference date to convert to a flag? – Fred May 01 '23 at 15:57
  • @Fred : I have edited above section, i want to update all other flags which is not satisfying above criteria as 'N' using same update so probably looking for a CASE WHEN condition and please let me know what i have tried is correct. – Debasis May 01 '23 at 16:21
  • I think MonthEndFiscal and HireDatePlus2Month_EndFiscal were intended to be the same name? Instead of RECORD_START_TS being a filter in "src" you want something like `SET ISNEWHIRE = CASE WHEN tgt.RECORD_START_TS <= src.HireDateStop THEN 'Y' ELSE 'N' END`. – Fred May 01 '23 at 23:19
  • Additionally to Fred's remark: Seems you want to add months not 30/60 days, April 9th + 60 days is not Jun 9th, but Jun 8th: join on `Add_Months(AGENT_HIRE_DATE,2) = DD.DATE_DT` and then it's `Add_Months(FISCAL_MONTH_END_DT, 1)`. – dnoeth May 02 '23 at 09:03
  • Thanks for the suggestions Fred & Dnoeth. I have used this update and that worked. UPDATE TGT FROM ABC.AGENT_DIM TGT, ( SEL AD.*, AD.AGENT_HIRE_DATE AS HireDateStart, Add_Months(AD.AGENT_HIRE_DATE,2) AS HireDatePlus2Month, DD.FISCAL_MONTH_END_DT AS HireDatePlus2Month_EndFiscal, Add_Months(HireDatePlus2Month_EndFiscal,1) AS HireDateStop FROM ABC.AGENT_DIM AD, ABC.DATE_DIM DD WHERE Add_Months(AD.AGENT_HIRE_DATE,2) = DD.DATE_DT ) SRC SET IS_NEW_HIRE = CASE WHEN CAST(TGT.RECORD_START_TS AS DATE) <= src.HireDateStop THEN 'Y' ELSE 'N' END WHERE TGT.NRDP_AGENT_ID = SRC.NRDP_AGENT_ID; – Debasis May 03 '23 at 15:15

0 Answers0