>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?
What is your SQL Server version? In SQL Server 2012 you will be able to get previous row with the LAG function. In other versions I would use ROW_NUMBER() function to get previous invoice.
If it's not broken, fix it until it is.
My Blog