Non-Insurance Balance By Place of Service, Date Range, and Provider
Back to Query Directory
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
rv_charges a
JOIN ptpayors b ON a.ptpayornum = b.ptpayornum
JOIN payors c ON b.payornum = c.payornum
JOIN poscodes d ON a.poscodenum = d.poscodenum
JOIN 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
a.provcode,a.lastname,a.firstname,a.id,SUM(a.chgsplbal) AS "Balance"
FROM
rv_charges a
JOIN ptpayors b ON a.ptpayornum = b.ptpayornum
JOIN payors c ON b.payornum = c.payornum
JOIN poscodes d ON a.poscodenum = d.poscodenum
JOIN 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
CategoryQueries
