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
