I have written a stored procedure that creates a snapshot table of my company's accounts receivable (AR) balance by customer. The customer AR balance changes in real time when charges, payments and adjustments are posted. This happens 24/7. We are a 24 hour business. The only time to capture the "day end" balance is when the system's CLOSED flag is set to 'Y'. I have a SQL view that captures the following fields:
DateTime -> Midnight of the Day Closed (2015-05-24 00:00:000)
ClosedDateTime -> Actual Time Midnight Close Completed (2015-05-25 00:23:000)
I know the exact time the system has closed for the previous day's business and creates a summary report. I have to report on the actual account balances to match this summary report.
From the data above, you can see the day being closed 5/24 closed a 12:23 AM on 5/25/15.
What I want to do is kick off my stored procedure as soon as possible to the time the system closed.
Is there a way to start running the stored procedure at 1 minute intervals after Midnight to check the values of the VIEW, and if the previous day has closed run the stored procedure. Of course the stored procedure should not run again after it has successfully run one time.
Can someone help with this?