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
Adding a Field in OD
Re: Adding a Field in OD
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;
Re: Adding a Field in OD
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
Thanks!
-Erik
Re: Adding a Field in OD
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.
Re: Adding a Field in OD
Yes I am aware of that, however I want to cut down the use of Excel sheets. Using way too many as is.
-Erik
-Erik