We have discovered that some credit entries were entered without specifying a Sort Code, which we need for reporting purposes. I need a total amount of such credits, by provider.
SELECT
r.provcode,
r.provlname,
SUM(amount) AS "TotalCredits"
FROM
sos.journal j,
sos.patients p,
sos.providers r
WHERE
j.ptnum=p.ptnum AND
p.providernum=r.providernum AND
j.trandate BETWEEN '2000-03-01' AND '2016-03-15' AND
j.sortcode IS NULL
GROUP BY
r.provcode, r.provlname