At my workplace, we use a SQL Server based application - Dynamics AX. Using this application, we create all our sales invoices. At the time of invoice creation, the system emails all our invoices to the customer when they are generated. This process also stores an archive copy on our file storage.
Our requirement has now changed to only store those invoices which are still outstanding and have not been paid. I have already come up with the TSQL that returns a list of all outstanding invoices - this list would look like the below:
The invoice archive copies are stored with a filename that corresponds to the InvoiceID, eg for invoice 254425, the filename would be 254425.pdf.
How can I use the list of unpaid invoices returned from the SQL query, to delete all files from the archive location whose filename does not match one of the items in the SQL query results? Ideally, I would want this task to run by itself every week without user intervention, unless there was an error in which case I get an email to say the task couldnt run
This problem has been bugging me for a while, so any help would be most appreciated