Open Dental Forum

Does anybody have a query that will give income by referral source? The queries that are in the examples only include income for referrals received in a given date range. What I need is to get income each month from each referral source regardless of when they were first seen. If their is residual income from that patient who was referred from the source I want to know about it each month.

This is what I have come up with by modifying a query example. I lists the patients from a given referral source for a given month and their payments. I can then export that to excel to get totals. If someone with more query experience than I sees any issues with this please let me know. Its doing some other stuff with an age range since I reused a previous query but I just said give me patients with age 1-150...


SET @FromDate='2014-01-01', @ToDate='2014-01-31' ;
SELECT LName, FName, (YEAR(CURDATE())-YEAR(Birthdate)) - (RIGHT(CURDATE(),5)<RIGHT(Birthdate,5)) AS 'Age',
(SELECT SUM(cp.InsPayAmt) FROM claimproc cp WHERE cp.PatNum=p.PatNum AND (cp.DateCP BETWEEN @FromDate AND
@ToDate) AND (cp.Status=1 OR cp.Status=4)) AS '$InsPayment',
(SELECT SUM(paysplit.SplitAmt) FROM paysplit WHERE paysplit.PatNum=p.PatNum AND paysplit.DatePay
BETWEEN @FromDate AND @ToDate GROUP BY paysplit.PatNum) AS '$PatPayment'
FROM patient p JOIN refattach ref ON ref.PatNum = p.PatNum
WHERE ref.ReferralNum = 2 AND
(YEAR(CURDATE())-YEAR(Birthdate)) - (RIGHT(CURDATE(),5)<RIGHT(Birthdate,5)) BETWEEN 1 AND 150
GROUP BY p.PatNum

Try this:
It will add a column with the referrer in the column. You can then click on any column header to sort by that column. If you want only the results for a specific referrer, you need to know the referralNum and then you can change the line Where ref.referralNum > 0. to Where ref.referralNum = x

SET @FromDate='2014-01-01', @ToDate='2014-01-31' ;
SELECT ReferralNum, LName, FName, (YEAR(CURDATE())-YEAR(Birthdate)) - (RIGHT(CURDATE(),5)<RIGHT(Birthdate,5)) AS 'Age',
(SELECT SUM(cp.InsPayAmt) FROM claimproc cp WHERE cp.PatNum=p.PatNum AND (cp.DateCP BETWEEN @FromDate AND
@ToDate) AND (cp.Status=1 OR cp.Status=4)) AS '$InsPayment',
(SELECT SUM(paysplit.SplitAmt) FROM paysplit WHERE paysplit.PatNum=p.PatNum AND paysplit.DatePay
BETWEEN @FromDate AND @ToDate GROUP BY paysplit.PatNum) AS '$PatPayment'
FROM patient p JOIN refattach ref ON ref.PatNum = p.PatNum
WHERE ref.ReferralNum > 0 AND
(YEAR(CURDATE())-YEAR(Birthdate)) - (RIGHT(CURDATE(),5)<RIGHT(Birthdate,5)) BETWEEN 1 AND 150
GROUP BY p.PatNum