CPT Code Count for Period and Payor

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

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

This site uses Akismet to reduce spam. Learn how your comment data is processed.