Revision [223]
Last edited on 2009-06-11 17:12:05 by SethKriegerAdditions:
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*/
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*/
Deletions:
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
Revision [222]
Edited on 2009-06-11 17:09:59 by SethKriegerAdditions:
======Non-Insurance Balance By Place of Service, Date Range, and Provider ======
Deletions:
Revision [221]
Edited on 2009-06-11 17:09:16 by SethKriegerAdditions:
======NonInsurance 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
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
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
Deletions:
comments here appear in italics
paste your tested query here
