I need some assistance in creating a query which shows the about expiry date in the account.

Possible this report will become daily report.

I need the report to run show to review six weeks before the expiry date.

I would like the report to show flag that column on its ‘date’ anniversary and a column regarding to previous one to three days in the case someone forgot to double check.

The columns would be like this:

NAME ie. John Doe,

EXPIRY_DATE ie 05/JUL/2024,

EARLIER_DATE ie 24/MAY/2024 = query ‘trunc (expiry_date) -42’ = 42 days represent six weeks = 7*6

FLAG_EARLIER = ‘x’
based on DATE ANNIVERSARY such as:
expiry_date is 5 July 2024, so six weeks earlier of its date will be 24 May 2024
on daily report produce on 24 May 2024 will be flag it to reminder to follow up
if report produce on 23 May 2024 won’t marked to flag it as well 25 May 2024 too

FLAG_FOLLOW_UP = ‘x’
as above to add after within three days to ensure one of staff member to double check again as our record has long account that has expiry date need to checking to ensure it is extending or closed then daily report produce on 25 May 2024 will marked to flag it until report dated 27 May 2024

Any help is appreciated!

TIA