;with cteInvoiceDetails as (select *, row_number() over (partition by InvoiceNo order by InvoiceDate DESC) as Rn) select * from cte c1 where Rn = 1 and NoStatus = 7 and exists (select 1 from cte c2 where c2.InvoiceNo = c1.InvoiceNo and c2.Rn = 2 and NoStatus = 3) -- so, the previous row has status 3 and therefore this row may be deleted>I have this for now but there is surely a way to simply this:
>IF OBJECT_ID('tempdb..#Temp') IS NOT NULL > DROP TABLE #Temp > >SELECT InvoiceDetail.NoInvoice,InvoiceDetail.Numero > INTO #Temp > FROM > (SELECT MAX(InvoiceDetail.Numero) AS InvoiceDetailNumero > FROM Invoice > INNER JOIN InvoiceDetail ON Invoice.Numero=InvoiceDetail.NoInvoice > WHERE Invoice.NoCompany=32 > GROUP BY InvoiceDetail.NoInvoice) Invoice2 > INNER JOIN InvoiceDetail ON Invoice2.InvoiceDetailNumero=InvoiceDetail.Numero > WHERE InvoiceDetail.NoStatus=7 AND InvoiceDetail.AddUser=1 > >DECLARE @NoInvoice INT >DECLARE @NoStatus INT >DECLARE @Numero INT > >WHILE (SELECT COUNT(*) FROM #Temp)>0 >BEGIN > > SELECT TOP 1 @Numero=Numero,@NoInvoice=NoInvoice FROM #Temp > > IF OBJECT_ID('tempdb..#Temp2') IS NOT NULL > DROP TABLE #Temp2 > > SELECT TOP 1 InvoiceDetail.Numero AS InvoiceDetailNumero,InvoiceDetail.NoStatus > INTO #Temp2 > FROM InvoiceDetail > WHERE InvoiceDetail.NoInvoice=@NoInvoice AND InvoiceDetail.NoStatus<>7 > ORDER BY InvoiceDetail.NoInvoice > > SELECT @NoStatus=NoStatus FROM #Temp2 > > IF (@NoStatus=3) > BEGIN > > DELETE FROM InvoiceDetail WHERE Numero=@Numero > > UPDATE Invoice SET NoStatus=3 WHERE Numero=@NoInvoice > > END > > DROP TABLE #Temp2 > > DELETE FROM #Temp WHERE Numero=@Numero >END > >DROP TABLE #Temp >