Does anybody have a query that will allow me to get a count of CPT code units by insurance plan (BCBS) over a specified time frame?
I specifically need to know which and how many CPT code units were billed to BCBS over the last 6 months.
This query produces a count of services for each CPT code for a specified period. The results are limited to service entries where there is at least one split to the designated payor. In this example, we are selecting any payor that has the word “BLUE” somewhere in the payor name: f.payorname LIKE ‘%BLUE%’ but you could use a specific name: f.payorname = ‘BCBS’ or payor number: f.payornum = 1234 or a group of payor numbers: f.payornum IN (1234, 5678, 123456) by changing the appropriate line in the WHERE clause below.
SELECT
d.cptcode AS "CPT",
COUNT(DISTINCT a.jnum) AS "SvcCount"
FROM
sos.journal a
JOIN sos.jcharges b ON a.jnum = b.jnum
JOIN sos.jchgsplits c ON b.jnum = c.jnum
JOIN sos.services d ON b.servicenum = d.servicenum
JOIN sos.ptpayors e ON c.ptpayornum = e.ptpayornum
JOIN sos.payors f ON e.payornum = f.payornum
WHERE
trandate BETWEEN '2002-01-01' AND '2002-03-31'
AND f.payorname LIKE '%BLUE%'
GROUP BY
d.cptcode
ORDER BY
d.cptcode