Fuzzy Matching Criteria for Bank Reconciliation
| Criterion | Logic | Confidence Level | Notes |
|---|---|---|---|
| Exact Amount + Narration contains LineItemText (or vice versa) | BankAmount = JEAmount AND (Narration LIKE LineItemText OR LineItemText LIKE Narration) | 90% | Strongest fuzzy match; textual overlap plus exact amount |
| Exact Amount + ReferenceID in Narration | BankAmount = JEAmount AND Narration LIKE ReferenceID | 75% | Moderate match; reference number appears in narration |
| Exact Amount + Narration similar to LineItemText | BankAmount = JEAmount AND DIFFERENCE(Narration, LineItemText) ≥ 3 | 50% | Phonetic similarity using SQL Server DIFFERENCE function |
| Exact Amount Only | BankAmount = JEAmount | 40% | Weak match; no textual similarity, only amount alignment |
| No Match | None of the above conditions met | 0% | Considered a weak or invalid match |
Scoring Formula
Each fuzzy rule contributes a confidence level. Final match selection uses ranking:
Highest FuzzyStrength → Exact amount alignment → Smallest date difference → JournalSequenceID tie-breaker.
Ranked by FuzzyStrength, then refined by amount/date proximity
Example:
- Exact Amount + Narration contains LineItemText → Confidence 90%
- Exact Amount + ReferenceID in Narration → Confidence 75%
- Exact Amount + Narration similar → Confidence 50%
- Exact Amount only → Confidence 40%
- No match → Confidence 0%
Top-ranked match per BankTransactionID is selected.
