Skip to Content
avatar image
Former Member

CRM Loyalty: Is anyone else experiencing mismatches between points total and balance?

We have been live for some three months and have experienced that on our system there are mismatches between the balance on a point account and the sum total of transactions posted into that account.

We have also checked with another client, and they have confirmed that this problem occurs on their system too, though to a lesser extent. In our case, some of the differences are quite large.

We have seen that when you do a merge on a count, the MERGE post has a zero value causing a balance mismatch, but even when you exclude merges, there are still instances of mismatches.

We have run the following query in DB01 -> SQL Editor to get a list of accounts where such a mismatch occurs. Please try on your side and see if you also get mismatches.

select a.object_id, a.balance, sum(tx.points),  a.balance - sum(tx.points) difference
from loyd_pt_acct as a
join loyd_pt_txn as tx
  on tx.ref_guid = a.guid
where a.object_id in (
select a.object_id
from loyd_pt_acct as a
join loyd_pt_txn as txn
  on txn.ref_guid = a.guid
group by a.object_id, a.balance
having a.balance <> sum(txn.points)
minus
select a.object_id
from loyd_pt_acct as a
join loyd_pt_txn as txn
  on txn.ref_guid = a.guid
where txn.txn_reason = 'MERGE'
group by a.object_id )
group by a.object_id, a.balance 

(Note that we are running large volumes of member activities through the loyalty engine in batch mode via program RLOY_ENGINE_SCHEDULE; we wonder if this has anything to do with our specific problem).

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • avatar image
    Former Member
    Oct 09, 2017 at 01:55 AM

    Hi Craig,

    We also encounter the same problem, we are also running large volumes of member activities through the loyalty engine in batch mode via program RLOY_ENGINE_SCHEDULE. How did you solve this mismatches then? Any feedback will be greatly appreciated!

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 17 at 05:03 AM

    Experienced similar and interested to hear how you may have solved.

    Add comment
    10|10000 characters needed characters exceeded