Payments.Completed not set for 6000 donations

[tiagonmas]

Describe the bug
Checking SQL below returns Paid donations but Payment is not set as completed

SELECT  top(10) dbo.Donations.*, dbo.Payments.*
FROM    dbo.Donations INNER JOIN
         dbo.Payments ON dbo.Donations.Id = dbo.Payments.DonationId
WHERE  (dbo.Donations.PaymentStatus = 1) AND (dbo.Payments.Completed Is NULL) order by Donations.id desc

several other are

[guerrerotook]

For me that query only return 10 rows in PROD, Am I missing something?

[tiagonmas]

its because of the Top 10 :) .... below will return around 6000

SELECT  top(10) dbo.Donations.*, dbo.Payments.*
FROM    dbo.Donations INNER JOIN
         dbo.Payments ON dbo.Donations.Id = dbo.Payments.DonationId
WHERE  (dbo.Donations.PaymentStatus = 1) AND (dbo.Payments.Completed Is NULL) order by Donations.id desc

[guerrerotook]

I found that the query that we should execute is this:

SELECT dbo.Donations.*, dbo.Payments.* FROM dbo.Donations INNER JOIN dbo.Payments ON dbo.Donations.ConfirmedPaymentId = dbo.Payments.Id WHERE (dbo.Donations.PaymentStatus = 1) AND (dbo.Payments.Completed Is NULL) order by Donations.id desc

And compared the ConfirmedPayId and the PaymentId so we only check payments that are the one confimed. That query is down to 3307 in PROD.

I am going to create a small tool to update this from the EasyPay API, this seem that we have an issue with the notifications.

[tiagonmas]

Those are 2 slightly different ways of doing the joins thus making it return different results ... but great anyway :)

[guerrerotook]

So I executed the tool, and we are down to 430 rows missing this completed date that have a missing completed date.

[guerrerotook]

The rest are from PayPal.

[tiagonmas]

my initial sql query was listing all payments that not complete, that were associated with a donation that was paid ... so we can ignore those. Your SQL is only looking at the confirmed payments and is more correct.
When running your SQL, I see that all donations that show up are from 2021.