Billed Service Counts by Charge Type
In version 2020.4 the Bill Generation grid’s columns for “Services w/ MRC” and “Services w/o MRC” do not show correct data despite the Bill process working correctly. PCR has removed these two columns in version 2020.4.4 and 2021.1, as was discussed in this previous Forum post. For more information on this change please refer to the Release Notes for 2021.1 or this follow-up Forum post. The following query can be used as an Adhoc or Export to obtain similar data for each bill.
MySQL
Billed Service Counts by Charge Type
SQL
SELECT
DATE_FORMAT(B.BILL_DATE, '%m/%d/%Y') AS BILL_DATE,
CASE WHEN B.TEST_BILL = 0 THEN 'Actual' ELSE 'Test' END AS BILL_TYPE,
BD.CHARGE_TYPE,
COUNT(DISTINCT BTS.SERVICES_RECID) SERVICES_CNT
FROM BILL_DETAILS BD
INNER JOIN BILL_TYPE_SERVICES BTS
ON BD.RECID = BTS.BILL_DETAILS_RECID
INNER JOIN BILLS B
ON B.RECID = BD.BILLS_RECID
WHERE B.TEST_BILL IS NOT NULL
GROUP BY B.RECID, B.BILL_DATE, B.TEST_BILL, BD.CHARGE_TYPEOracle
Billed Service Counts by Charge Type
SQL
SELECT
TO_CHAR(B.BILL_DATE, 'MM/DD/YYYY') AS BILL_DATE,
CASE WHEN B.TEST_BILL = 0 THEN 'Actual' ELSE 'Test' END AS BILL_TYPE,
BD.CHARGE_TYPE,
COUNT(DISTINCT BTS.SERVICES_RECID) SERVICES_CNT
FROM BILL_DETAILS BD
INNER JOIN BILL_TYPE_SERVICES BTS
ON BD.RECID = BTS.BILL_DETAILS_RECID
INNER JOIN BILLS B
ON B.RECID = BD.BILLS_RECID
WHERE B.TEST_BILL IS NOT NULL
GROUP BY B.RECID, B.BILL_DATE, B.TEST_BILL, BD.CHARGE_TYPE