Dynamically Generate Anniversary Dates From HireDate

[KD_]

Hello folks,

I have a requirement to generate a couple of flag fields displaying....

1. Recurring annual tests due date for each employee based on their HireDate
2. Recurring 5 year tests due date for each employee also based on their HireDate.

So if the employee was hired on Feb-16-2023, the annual test due date field will show Feb-16-2024, & thereafter, Feb-16-2025 & so forth. The same will apply to the 5 year increments.

For the first iteration I am aware that we can use AddYears() function, but I am not familiar with how we can dynamically generate dates for the recurring dates. Please help. Thank you

[MarcoWedel]

can you please post a sample of the resulting table including the flag fields as well as the corresponding source table? thanks

[KD_]

Yes, thank you Marco. Here is what I came up with, which shows how the table would currently look after the initial load & then how it would look as each employee pass their first anniversary of both the annual and the 5 year durations after their hire date.

I hope it make sense. Thank you

[Vegar]

Something like this maybe?

Load ID , HireDate, 

Addyears(HireDate, iterno()) as AnniversaryDate,

If(iterno()=round(iterno(),5), Addyears(HireDate, iterno()),null()) as Anniversary5Yrs

FROM Source 

While iterno()<30;

[KD_]

Thank you Vegar, this is almost a correct solution, but it is iterating more than once at reload, as you can see here

Ideally, the script should only iterate once & display 6/16/2024 for the Anniversary field & 06/16/2026 for the Anniversary5Yrs field as of today. Only after today() is respectively past any of these 2 dates, should the dates update to 06//16/2027 for the Anniversary & 06/16/2031 for the 5yr one. I hope this makes it more clearer. Thank you