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:
- erase all test results that are older than 14 days
- 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?