Referring Physicians for Payors with ID’s and Patient Count

I'm writing a query and want to print out medicare and bcbs numbers in addition to default id for referral
sources. I can get the defaultid from refsrcs but the other numbers come up as null. I upgraded to the
latest version of OM and it now looks like there is a different system for entering specific insurance
numbers for referral sources so I was wondering if this is a factor? Also is there a way to get a count for
the number of patients a particular referring physician has sent our way?
OK, looks like you just want referral sources associated with active patients based on intake date. Couple
of things you need to consider...

Firstly, the referring physicians used on claims may not match the referral source stored in Patients. The
latter could be "yellow pages", former patients, or whatever, while the former should be only physicians
or other healthcare providers. Given that you are interested in the ID's, it seems that you are probably
more interested in the referring physician that prints on the claim forms. That is in the claim setup table
(PtCSU) not in Patients. To get to those, you would have to add a JOIN to PtCSU. In addition you will
have to (left outer) join refpayorids and payors:
SELECT
 trim(refname + ', ' + b.firstname) as "RefName",
 b.address1,
 b.address2,
 b.city,
 b.zip,
 b.phone,
 b. Defaultid,
 e.payorname,
 d.providerid,
 d.secondaryid,
 COUNT(distinct a.ptnum) AS "PtCount"
FROM
 sos.patients A
 JOIN sos.ptcsu c ON c.ptnum = a.ptnum
 JOIN sos.refsrcs b ON b.refsrcnum = c.refsrcnum
 LEFT OUTER JOIN sos.refpayorids d ON b.refsrcnum = d.refsrcnum
 LEFT OUTER JOIN sos.payors e ON e.payornum = d.payornum
WHERE
 a.flag=0
 AND a.intakedate > '1980-10-01'
 AND a.licnum='101'
 AND e.payornum IN (101,102)
GROUP BY
 refname,
 b.address1,
 b.address2,
 b.city,
 b.zip,b.phone,
 b.defaultid,
 e.payorname,
 d.providerid,
 d.secondaryid
ORDER BY
 refname
;OUTPUT TO c:\sos\refinfo.html FORMAT HTML

	

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.