Non-Insurance Balance By Place of Service, Date Range, and Provider

I am looking for a query that prints out total balance remaining on patients only (not insurance) by LOC code (e.g.,11 or 61) by date range by provider.

The following query gives the balance itemized by patient. To get just summary totals, remove “a.lastname,a.firstname,a.id” from the SELECT and GROUP BY clauses.

SELECT
a.provcode,a.lastname,a.firstname,a.id,SUM(a.chgsplbal) AS "Balance"
FROM
sos.rv_charges a
JOIN sos.ptpayors b ON a.ptpayornum = b.ptpayornum
JOIN sos.payors c ON b.payornum = c.payornum
JOIN sos.poscodes d ON a.poscodenum = d.poscodenum
JOIN sos.patients e ON a.ptnum = e.ptnum
WHERE
a.licnum = 101 AND   /*look only at main data set*/
e.flag = 0 AND          /* just active list patients*/
c.payortype <> 'I'     /* ignore insurance splits*/
AND d.defcode IN ('11','61')    /* place of service code is 11 or 61*/
AND a.trandate BETWEEN '2009-01-01' AND '2009-03-31'    /* date range*/
GROUP BY
a.provcode,a.lastname,a.firstname,a.id

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.