Below is the query which gives the error records. SELECT Count (*) FROM (SELECT cc_bulkinvoice.id FROM cc_bulkinvoice WHERE cc_bulkinvoice.status NOT IN ( 17, 23 ) AND (SELECT ( CASE WHEN Sum (bii.amount) IS NOT NULL THEN Sum (bii.amount) ELSE 0 END ) FROM cc_bulkinvoiceitem bii WHERE bii.bulkinvoiceid = cc_bulkinvoice.id AND bii.retired = 0) <> cc_bulkinvoice.totaltransactionamount) a
Below is the sql update script to resolve the error records: USE ClaimCenter;
begin transaction
update cc_bulkinvoice
set cc_bulkinvoice.TotalTransactionAmount = t2.sumAmt
from cc_bulkinvoice b inner join (
select bii.BulkInvoiceID,sum(bii.Amount) as sumAmt from cc_bulkinvoiceitem bii
where bii.Retired = 0
group by bii.BulkInvoiceID
) as t2 on t2.BulkInvoiceID = b.ID
where b.id in (26841,26849,26850,26856,13036,13037,2003,2214)
--ROLLBACK TRANSACTION;
COMMIT TRANSACTION;
Even after running the db script to update the records, the query is still returning error records. Could anyone please help in identifying what is wrong with the update script.
The error in your SQL script is not immediately obvious to me. The way I would go about troubleshooting this would be to one-by-one manually figure out what the bulk invoice total should be on the cc_bulkinvoice parent table should be and then manually add-up the non-retired cc_bulkinvoiceitem rows and see what number you get.
The one thing that the query to find the bad bulk invoices does that your update query does not do is a null check. So if for example one of your bulk invoices was like this:
but...
Then your update query will not work because it does not work in the case where there are 0 non-retired invoice items