Programatically check if a date-period has expired and possibly add new row to a table, daily.

Question

I have a table, named ReportingPeriods, that I want to be populated entirely automatically. In another table, there are attributes CreationDate and ReportingPeriodLength which together will determine the the results of this row. Upon adding a row for this table, we will add a row in ReportingPeriods with the first StartDate as the creation date and EndDate as ReportingPeriodLength days after startDate.

So far, this is simple. However, I wish to check (daily) whether this period has expired. And if it has, I want to start a new row, beginning the day after the EndDate and extending, once again, for ReportingPeriodLength days.

Is this possible to accomplish without manually checking and adding a new row?

Thanks :)

Answer 1

call a stored procedure from a database job that is run each day. within this procedure, do your check and insert if necessary.

Answer 2

Windows Scheduled Tasks is an appropriate tool for this.

You can write a simple script or a C# console application that calls a stored procedure in your database. Add that executable as the command for a new scheduled task and pick an appropriate time.