Reconcile March with the payment processor
The shop’s records say what each of the five paid March orders should have been charged: the lines total plus the shipping fee. The payment processor’s export lists what it actually collected for those orders. Do the two agree, and if not, where do they differ?
Starting state
Our side: orders 102, 103, 106, 107 and 108, with lines totals of 54, 32, 40, 20 and 34 and shipping fees of 0, 5, 0, 5 and 5. Processor side: five charges, 54 for order 102, 37 for 103, 40 for 106, 25 for 107 and 34 for 108.
Count the rows on each side.
Why this step
A missing or extra order would change every later number, so rule it out first.
What changes
Five orders on our side and five charges in the export, with the same five order numbers.
Compute each side’s total.
Why this step
A total is the cheapest comparison. If it matches you still look at rows; if not, you know there is something to find.
What changes
Ours: 180 from the lines plus 15 in shipping, 195. Export: 190. The two are 5 apart.
Compare order by order, expected minus charged, with the query below.
Why this step
A gap of 5 could be one order off by 5, or two orders off by amounts that partly cancel. Only per-order differences can tell those apart.
What changes
Orders 102, 103, 106 and 107 differ by 0. Order 108 differs by 5: we expected 39, the processor collected 34.
Open order 108 in the source tables.
Why this step
The difference now has exactly one row to explain.
What changes
Its lines are a Kettle at 30 and a Filter at 4, which add to 34, and its shipping fee is 5. The processor’s 34 equals the lines alone.
Result
The query was not wrong. The processor’s 34 equals the lines alone, so the likeliest explanation is that the 5 of shipping was not charged on this order. Confirm that in the order record or with the processor before you report it. Revenue from the lines, 180, is unaffected, but a report of money collected must say 190, or say that 5 of the 195 expected was not collected.