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