I have a query that returns a list of missing ClientIDs from reports. This query works and provides the correct data.

I need to have two additional fields added to this, unfortunately I don’t have access to any logs etc to run this but I can use Airflow if needed.

The output would just be: ClientID, ClientReportName, ID_First_Found, ID_Updated

The first field is ID_First_Found:

• This would be the date that the ClientID was first found to be missing in the report
• I have tried to run CURRENT_TIMESTAMP() but whenever the report is re-run the date updates (which I understand) but I need it to only be the first time it was found/the first time the report was run

Based on these missing IDs we would then update the ClientIDs in reports to fix these missing ones.

The second field would then be ID_Updated:

• The report would then be re-run
• This field would return the date that the report was run and the ClientID was found in the report

I’ve mostly tried different placements of CURRENT_TIMESTAMP to see what happens when I re-run things and it still gives me the most up to date time. It just updates to the existing time instead.

This is the structure for building the report, I’ve changed some names around (which is why it looks like the CREATE step has an error) but this is basically what I’m trying.

I also tried with using the query in Airflow but it’s giving me the same results.

I know what I want to do for this but I’m not sure how to actually structure it.