Identifying Accounts with Unapplied “Balance Forward” Amounts

During the conversion of data from the old DOS software to the Windows version of SOS, one option was to create a dummy payor called “Balance Forward” and initialize it with the total balance from the old software. Years later, some customers have had difficulties determining accurate account balances because of credit amounts that linger as unapplied payments attributed to the Balance Forward payor. The following query lists the accounts showing this issue.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

SELECT 
  fullnameid, 
  licnum AS dataset, 
  (IF flag = 0 THEN 'active' ELSE 'inactive' ENDIF) AS PtList,
  SUM(crsplamt) AS Tot_Unapplied
FROM 
  rv_creditsplits
WHERE 
  payornum = 2
AND 
  (chgsplnum = 0 OR chgsplnum IS NULL)
GROUP BY 
  fullnameid, licnum, ptlist
ORDER BY 
  fullnameid