.Counts of Services on a Bill based on Charge Type v2020.4

In version 2020.4 the Bill Generation grid’s columns for “Services w/ MRC” and “Services w/o MRC” do not show the correct data, despite the Bill process working correctly.  PCR determined the best course was to remove 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, the Release Notes for 2020.4, or this follow-up Forum post.  The following query can be used as an AdHoc or Export to obtain similar data for each Bill if your Organization was utilizing these fields.  

Counts of Services on a Bill based on Charge Type

-- This query is valid for MySQL AND Oracle 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, BD.CHARGE_TYPE