Adding a Field in OD

[Erik@AD]

List -> Referrals

If I want to see a count (patients referred FROM this referral) of referrals in this list without double clicking on a name, would that be a Feature Request or something "easy" to program?
Ideally this referral list will be sort able by count.

-Erik

[cmcgehee]

It would be fairly easy to add a count to the Referrals list, but we would still go through the Feature Request process to add it. Alternatively, you could use a query to get the count of the patients from each referral. Here is 501 from our website.

Code: Select all

/*501 Count of patients from each 'referred from' source in given date range 
(determined by date of 1st visit -- not referral attach date), with sums of:
fees for procs completed on day of 1st visit.
payments from that patient on day of 1st visit.
claim amts paid for procs completed on day of 1st visit. (calculated by claim procedure level procedure date)
fees for treatment planned procedures planned within one week of 1st visit*/
/*This query is unusual and should be understood before the data is used*/
/*WARNING: On versions before 14.3.1 that are using replication, do not run this query from multiple computers at the same time or replication will crash.*/
SET @FromDate='2017-01-01' , @ToDate='2017-02-28';
DROP TABLE IF EXISTS tmp;
CREATE TABLE tmp
SELECT A.PatNum, DATE(MIN(pl.ProcDate)) AS DateFV
FROM procedurelog pl 
INNER JOIN (/*patients seen in date range*/SELECT pl.PatNum
FROM procedurelog pl
WHERE pl.ProcDate  BETWEEN @FromDate AND @ToDate AND pl.ProcStatus=2/*complete*/) A ON
A.PatNum=pl.PatNum
WHERE pl.ProcStatus=2/*complete*/
GROUP BY pl.PatNum
HAVING DATE(MIN(pl.ProcDate)) BETWEEN @FromDate AND @ToDate;
SELECT B.ReferraLName, COUNT(*) NumPats,
SUM(B.1VisFees) '$1VisFees',
SUM(B.1VisPatPay) '$1VisPatPay',
SUM(B.1VisInsPay) '$1VisInsPay',
SUM(B.1WeekTP) '$1WeekTP',
SUM(B.1VisPatPay)+ SUM(B.1VisInsPay) '$1VisTotPay'
FROM (
SELECT tmp.DateFV, tmp.PatNum,(SELECT CONCAT(r.LName, ',', r.FName) AS RefName FROM referral r 
LEFT JOIN refattach ra ON r.ReferralNum=ra.ReferralNum WHERE tmp.PatNum=ra.PatNum 
AND ra.IsFrom='1' GROUP BY ra.PatNum) AS 'ReferraLName',
(SELECT SUM(pl.ProcFee*(pl.UnitQty+pl.BaseUnits)) FROM procedurelog pl WHERE pl.PatNum=tmp.PatNum AND pl.ProcStatus=2 AND DATE(pl.ProcDate)=tmp.DateFV) AS '1VisFees',
(SELECT  SUM(SplitAmt) FROM paysplit WHERE tmp.PatNum=paysplit.PatNum AND (DatePay=tmp.DateFV)) AS '1VisPatPay',
(SELECT  SUM(InsPayAmt) FROM claimproc cp WHERE cp.PatNum=tmp.PatNum AND cp.ProcDate=tmp.DateFV) AS '1VisInsPay',
(SELECT SUM(pl.ProcFee*(pl.UnitQty+pl.BaseUnits)) FROM procedurelog pl WHERE pl.PatNum=tmp.PatNum AND (pl.ProcStatus=2 OR pl.ProcStatus=1) AND DATE(pl.DateTP)<=(tmp.DateFV +INTERVAL 7 DAY)) AS '1WeekTP'
FROM tmp) B
GROUP BY B.ReferraLName
ORDER BY NumPats DESC;
DROP TABLE IF EXISTS tmp;

[Erik@AD]

I will put in a Feature Request, since with that option I can make notes, which is not an option with query results.

Thanks!

-Erik

[JasonL]

This may already be known, but you can also export your query results to a txt or xls file type so you can open it using MS Excel. From there you can make notes as needed, format the results, and make use of various Excel functions.

[Erik@AD]

Yes I am aware of that, however I want to cut down the use of Excel sheets. Using way too many as is.

-Erik