In SOS SortCode values can be assigned to charge entries, credit entries, or both. As a result, there are a couple of ways to group and sort credits by SortCode. The following queries provide totals by giving priority to either the SortCode in the credit entry, or to the SortCode entered in the charge to which the credit split is applied. There is also the possiblity that a payment credit may have been entered, but not assigned as yet to a charge, as in the case of a pre-payment.
In the following query, the sortcode entered directly on the credit is represented by lu1.lucode. The SortCode on the charge to which it is applied (if it has been applied) is lu2.lucode. If neither value is available for a particular credit split, then it will be assigned the value ‘Unspecified’. The credit/charge priority is easily reversed by simply reversing the order of the lu1.sortcode and lu2.sortcode fields in the COALESCE function in line 2. Remember that the COALESCE function simply returns the value of the first parameter that is not NULL. In other words, if the first sortcode specified was not entered by the user, then it will check to see if other one was. If neither one is present, then it returns the third parameter, which in this case is the string “Unspecified”.
SELECT
COALESCE(lu1.lucode,lu2.lucode,'Unspecified') AS "SortCode",
SUM (c.crsplamt) AS "Total Credit"
FROM
sos.journal a
JOIN sos.jcredits b ON a.jnum = b.jnum
JOIN sos.jcrsplits c ON b.jnum = c.jnum
LEFT OUTER JOIN sos.lookups lu1 ON a.sortcode = lu1.lunum
LEFT OUTER JOIN sos.jchgsplits d ON c.chgsplnum = d.chgsplnum
LEFT OUTER JOIN sos.journal e ON d.jnum = e.jnum
LEFT OUTER JOIN sos.lookups lu2 ON e.sortcode = lu2.lunum
WHERE a.trandate BETWEEN '2000-01-01' AND '2011-12-31'
GROUP BY "SortCode"
