Revision [222]
This is an old revision of NonInsuranceBalanceByPOSDateProvider made by SethKrieger on 2009-06-11 17:09:59.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
