I have a daily report being emailed to our Servicenow that has upcoming Offboarding users. There is an EVENT DATE which in theory is the date the HR system was notified of the upcoming termination, as well as a TERM DATE. I am being asked to send an email out to users that are "soon to be offboarded". I am coalescing on the user, and capturing event and term dates into fields on sys_user (currently- This may not be best option).
The problem I am running into is that this daily report is a cumulative report of all offboards for last few years and only getting bigger. What is the best way to only process records that are new/updated/future? Is there some sort of script I can add to the transform map (or wherever) to filter on the dates I have?
Original plan is to use a scheduled job nightly to send this email out a couple weeks prior to the TERM DATE, or if TERM DATE is less than that, immediately.
