Transactions with Missing Sort Codes for Period

The following query produces a list of charge entries for which no sort code was selected during data entry. Be sure to modify the date range to the period that you want to examine.

SELECT 
  lastname, firstname, id, jnum, trandate, provcode, srvcode, amount, adduser 
FROM 
  sos.rv_charges
WHERE 
  sortcode IS NULL
  AND trandate BETWEEN '2013-01-01' AND '2013-03-31'
ORDER BY 
  lastname,firstname,id,trandate

Credits are a bit more complicated. If a creditsplit has been applied, and no sort code has been specified for the entire credit entry, some SOS reports will trace back to the charge being paid and report the amount of the creditsplit with the linked charge’s sort code. The following query ignores the linked charge and reports based on the absence of a specified sort code on the credit itself.

SELECT 
  pt.lastname, pt.firstname, pt.id, jou.jnum, jou.trandate, cre.credtype, jou.amount, jou.adduser 
FROM 
  sos.journal jou 
  JOIN sos.jcredits cre
  JOIN sos.patients pt ON jou.ptnum = pt.ptnum
WHERE 
  sortcode IS NULL
  AND trandate BETWEEN '2013-01-01' AND '2013-03-31'
ORDER BY 
  lastname,firstname,id,trandate

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

This site uses Akismet to reduce spam. Learn how your comment data is processed.