I have a very specific operation I need to apply on a table to fix some data and remove some records.
There is a master table Invoice. Then, I have child records in InvoiceDetail.
We need to rollback a procedure that was done a few months ago because the requirements have been reversed.
Basically, the InvoiceDetail records contain many records related to an Invoice and they have a NoStatus field.
For every Invoice, I need to remove the last InvoiceDetail record if its NoStatus=7 but only if the previous InvoiceDetail record has NoStatus=3.
I have been looking at various alternatives but I haven't found one that was conclusive so far.
Anyone would have an idea of what kind of approach could be used here?