Revision [179]
Last edited on 2008-11-14 15:31:33 by SethKriegerNo differences.
Revision [178]
Edited on 2008-11-14 15:31:06 by SethKriegerAdditions:
"AmtChargedPayor" <> 0
AND (a.paiddate IS NULL OR a.paiddate > '2008-09-30')
AND (a.paiddate IS NULL OR a.paiddate > '2008-09-30')
Deletions:
Revision [177]
Edited on 2008-11-14 15:21:28 by SethKriegerAdditions:
======Detailed List of Charge Balances by Payor On Specified Date======
We have an audit coming up and would like to have a detailed listing, down to the charge split level, of unpaid charges on the last day of our fiscal year. It would be most helpful to have the list sorted by payor and date.
In the following query, you must replace the date in two places with the last date of your fiscal period. Remember that this is a listing of charge splits, so there can be several lines for the same service item, even for a single payor. The sort order keeps splits for the same payor and service together.
SELECT
//patient
a.licnum AS "DataSet",
a.id AS "AccountID",
a.lastname AS "LastName",
a.firstname AS "FirstName",
//service
a.jnum AS "TransactionNum",
a.trandate AS "SrvDate",
a.provcode AS "ProvCode",
a.srvcode AS "SrvCode",
a.amount AS "TotalFee",
a.balance AS "TotalBalance",
//payor
TRIM(c.payorname + ' ' +c.firstname) AS "Payor",
a.chgsplamt AS "AmtChargedPayor",
(SELECT COALESCE(SUM(crsplamt),0)
FROM jcrsplits
WHERE chgsplnum = a.chgsplnum AND dateapplied <= '2008-09-30') AS "PayorCreditsApplied",
(a.chgsplamt - "PayorCreditsApplied") AS "PayorBalance",
//billing dates
COALESCE(STRING(a.firstbilled),'') AS "DateFirstBilled",
COALESCE(STRING(a.lastbilled),'') AS "DateMostRecentlyBilled"
FROM
rv_charges a
JOIN ptpayors b ON a.ptpayornum = b.ptpayornum
JOIN payors c ON b.payornum = c.payornum
WHERE
"AmtChargedPayor" <> 0 AND
(a.paiddate IS NULL OR a.paiddate > '2008-09-30')
ORDER BY
"Payor","SrvDate","TransactionNum"
The following version combines splits if a single payor has more than one split on the same service charge entry.
SELECT
//patient
a.licnum AS "DataSet",
a.id AS "AccountID",
a.lastname AS "LastName",
a.firstname AS "FirstName",
//service
a.jnum AS "TransactionNum",
a.trandate AS "SrvDate",
a.provcode AS "ProvCode",
a.srvcode AS "SrvCode",
a.amount AS "TotalFee",
a.balance AS "TotalBalance",
//payor
TRIM(c.payorname + ' ' +c.firstname) AS "Payor",
SUM(a.chgsplamt) AS "AmtChargedPayor",
SUM((SELECT COALESCE(SUM(crsplamt),0)
FROM jcrsplits
WHERE chgsplnum = a.chgsplnum AND dateapplied <= '2008-09-30')) AS "PayorCreditsApplied",
("AmtChargedPayor" - "PayorCreditsApplied") AS "PayorBalance",
//billing dates
MIN(COALESCE(STRING(a.firstbilled),'')) AS "DateFirstBilled",
MAX(COALESCE(STRING(a.lastbilled),'')) AS "DateMostRecentlyBilled"
FROM
rv_charges a
JOIN ptpayors b ON a.ptpayornum = b.ptpayornum
JOIN payors c ON b.payornum = c.payornum
WHERE
(a.paiddate IS NULL OR a.paiddate > '2008-09-30')
GROUP BY
"DataSet","Payor","Lastname","Firstname","ID","SrvDate","ProvCode",
"SrvCode","TotalFee","TotalBalance","TransactionNum"
HAVING
"AmtChargedPayor" <> 0
AND "PayorBalance" <> 0
ORDER BY
"DataSet","Payor","SrvDate","TransactionNum"
We have an audit coming up and would like to have a detailed listing, down to the charge split level, of unpaid charges on the last day of our fiscal year. It would be most helpful to have the list sorted by payor and date.
In the following query, you must replace the date in two places with the last date of your fiscal period. Remember that this is a listing of charge splits, so there can be several lines for the same service item, even for a single payor. The sort order keeps splits for the same payor and service together.
SELECT
//patient
a.licnum AS "DataSet",
a.id AS "AccountID",
a.lastname AS "LastName",
a.firstname AS "FirstName",
//service
a.jnum AS "TransactionNum",
a.trandate AS "SrvDate",
a.provcode AS "ProvCode",
a.srvcode AS "SrvCode",
a.amount AS "TotalFee",
a.balance AS "TotalBalance",
//payor
TRIM(c.payorname + ' ' +c.firstname) AS "Payor",
a.chgsplamt AS "AmtChargedPayor",
(SELECT COALESCE(SUM(crsplamt),0)
FROM jcrsplits
WHERE chgsplnum = a.chgsplnum AND dateapplied <= '2008-09-30') AS "PayorCreditsApplied",
(a.chgsplamt - "PayorCreditsApplied") AS "PayorBalance",
//billing dates
COALESCE(STRING(a.firstbilled),'') AS "DateFirstBilled",
COALESCE(STRING(a.lastbilled),'') AS "DateMostRecentlyBilled"
FROM
rv_charges a
JOIN ptpayors b ON a.ptpayornum = b.ptpayornum
JOIN payors c ON b.payornum = c.payornum
WHERE
"AmtChargedPayor" <> 0 AND
(a.paiddate IS NULL OR a.paiddate > '2008-09-30')
ORDER BY
"Payor","SrvDate","TransactionNum"
The following version combines splits if a single payor has more than one split on the same service charge entry.
SELECT
//patient
a.licnum AS "DataSet",
a.id AS "AccountID",
a.lastname AS "LastName",
a.firstname AS "FirstName",
//service
a.jnum AS "TransactionNum",
a.trandate AS "SrvDate",
a.provcode AS "ProvCode",
a.srvcode AS "SrvCode",
a.amount AS "TotalFee",
a.balance AS "TotalBalance",
//payor
TRIM(c.payorname + ' ' +c.firstname) AS "Payor",
SUM(a.chgsplamt) AS "AmtChargedPayor",
SUM((SELECT COALESCE(SUM(crsplamt),0)
FROM jcrsplits
WHERE chgsplnum = a.chgsplnum AND dateapplied <= '2008-09-30')) AS "PayorCreditsApplied",
("AmtChargedPayor" - "PayorCreditsApplied") AS "PayorBalance",
//billing dates
MIN(COALESCE(STRING(a.firstbilled),'')) AS "DateFirstBilled",
MAX(COALESCE(STRING(a.lastbilled),'')) AS "DateMostRecentlyBilled"
FROM
rv_charges a
JOIN ptpayors b ON a.ptpayornum = b.ptpayornum
JOIN payors c ON b.payornum = c.payornum
WHERE
(a.paiddate IS NULL OR a.paiddate > '2008-09-30')
GROUP BY
"DataSet","Payor","Lastname","Firstname","ID","SrvDate","ProvCode",
"SrvCode","TotalFee","TotalBalance","TransactionNum"
HAVING
"AmtChargedPayor" <> 0
AND "PayorBalance" <> 0
ORDER BY
"DataSet","Payor","SrvDate","TransactionNum"
Deletions:
comments here appear in italics
paste your tested query here
