>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?
Not sure whether you mean "Any previous invoice detail record" or "The previous invoice detail record". Your example in another post just selects the TOP 1 of any record where NoStatus < > 7
But I'd give it a shot:
To get the invoiceDetails were for the last record NoStatus = 7 and has any previous record with NoStatus = 3
SELECT InvoiceDetail.NoInvoice,InvoiceDetail.Numero
FROM InvoiceDetail I
WHERE NoStatus = 7 AND NOT EXISTS(SELECT 1 FROM InvoiceDetail WHERE I.NoInvoice = NoInvoice AND Numero > I.Numero)
AND EXISTS(SELECT 1 FROM InvoiceDetail WHERE I.NoInvoice = NoInvoice AND NoStatus = 3)
If it needs to be the previous record and numero is one less than numer of the last record
SELECT InvoiceDetail.NoInvoice,InvoiceDetail.Numero
FROM InvoiceDetail I
WHERE NoStatus = 7 AND NOT EXISTS(SELECT 1 FROM InvoiceDetail WHERE I.NoInvoice = NoInvoice AND Numero > I.Numero)
AND EXISTS(SELECT 1 FROM InvoiceDetail WHERE I.NoInvoice = NoInvoice AND NoStatus = 3 AND Numero = I.Numero-1)
Is this what you are looking for ?
Walter