Daily task: delete rows update other table

1

Hello,

I'm fairly new to SQL and am trying to learn with some 'simple' projects.

I have a database with 2 tables, one is a table of students (ID, Name, testStatus, dateLastTest,) and one for Covid tests (ID,studentID,testDate, testResult)

Users are using Power Apps to add new records to the tests table and update the testStatus of the student.

Now, I want to do 2 daily tasks:

  1. erase all test results that are older than 14 days
  2. update the students' status to "untested" if there is no record of a test for that student left (because we just deleted one that was 15 days ago and there is no newer one)

I can do this in Power Automate and have a recurring flow handle this.
However, it seems 'cleaner' having the SQL server do this on its own without outside connections.

Can this be done? How would I tackle this?
Thank you!

2

Yes, you can automate this as per your schedule through a SQL agent job with 2 steps.
you may get better solutions here.

Step 1: Delete records older than 14 days from Tests table.
Delete from Tests
WHERE testDate< DATEADD(day, -14, GETDATE());

Step 2: Update students table.
Update students set testStatus='untested' WHERE ID not in(select studentID from tests);