Page 1 of 1
monthly production by provider
Posted: Tue Oct 23, 2007 1:22 pm
by abelaguilar
Can someone post a query that will give me a monthly production report by provider?
Currently you have to run a report separately by provider which is a PIA. especially since I need to go back and do 4 months.
Re: monthly production by provider
Posted: Wed Aug 26, 2009 4:59 pm
by V Suite
Greetings
Is there such a query please such that the start and end dates or the month could be designated, and the columns would show Production, WriteOff, Adjustments, TotalProduction, Income, InsIncome, TotalIncome, while the rows show each provider?
Thanks
Re: monthly production by provider
Posted: Wed Aug 26, 2009 6:03 pm
by jordansparks
That's request 830
Re: monthly production by provider
Posted: Thu Aug 27, 2009 4:02 am
by V Suite
Thanks for the reply. I take it a query will not do it, and it must be programmed in?
Re: monthly production by provider
Posted: Thu Aug 27, 2009 5:59 am
by jordansparks
Oh. Yes, a query would work, but boy would it be big and messy.
Re: monthly production by provider
Posted: Tue Dec 08, 2009 12:59 pm
by V Suite
This Query is almost what I want in my office. Can someone tell me how to add a Total Production column after adjustments? Cheers.
SET @FromDate='2009-08-01' , @ToDate='2009-08-31';
SELECT ProvNum, (SELECT SUM(ProcFee) FROM procedurelog WHERE procedurelog.ProcStatus=2 AND procedurelog.ProvNum=provider.ProvNum AND procedurelog.procdate BETWEEN @FromDate AND @ToDate) AS '$Production',
(SELECT SUM(AdjAmt) FROM adjustment WHERE adjustment.ProvNum=provider.ProvNum AND adjustment.AdjDate BETWEEN @FromDate AND @ToDate) AS '$Adjustment',
(SELECT SUM(SplitAmt) FROM paysplit WHERE paysplit.ProvNum=provider.ProvNum AND paysplit.DatePay BETWEEN @FromDate AND @ToDate) AS '$Income'
FROM provider
Re: monthly production by provider
Posted: Tue Dec 08, 2009 3:43 pm
by jordansparks
Interesting query.
SET @FromDate='2009-08-01' , @ToDate='2009-08-31';
SELECT ProvNum, (SELECT SUM(ProcFee) FROM procedurelog WHERE procedurelog.ProcStatus=2 AND procedurelog.ProvNum=provider.ProvNum AND procedurelog.procdate BETWEEN @FromDate AND @ToDate) AS $Production,
(SELECT SUM(AdjAmt) FROM adjustment WHERE adjustment.ProvNum=provider.ProvNum AND adjustment.AdjDate BETWEEN @FromDate AND @ToDate) AS $Adjustment,
'$Production'+'$Adjustment',
(SELECT SUM(SplitAmt) FROM paysplit WHERE paysplit.ProvNum=provider.ProvNum AND paysplit.DatePay BETWEEN @FromDate AND @ToDate) AS $Income
FROM provider
Re: monthly production by provider
Posted: Wed Dec 09, 2009 2:49 am
by V Suite
Nope, that doesn't work. It gives all zeroes for the Total Production. But this works. Yay me!
SET @FromDate='2009-08-01' , @ToDate='2009-08-31';
SELECT ProvNum, (SELECT SUM(ProcFee) FROM procedurelog WHERE procedurelog.ProcStatus=2 AND procedurelog.ProvNum=provider.ProvNum AND procedurelog.procdate BETWEEN @FromDate AND @ToDate) AS $Production,
(SELECT SUM(AdjAmt) FROM adjustment WHERE adjustment.ProvNum=provider.ProvNum AND adjustment.AdjDate BETWEEN @FromDate AND @ToDate) AS $Adjustment,
(SELECT $Production + $Adjustment) AS $TotalProduction,
(SELECT SUM(SplitAmt) FROM paysplit WHERE paysplit.ProvNum=provider.ProvNum AND paysplit.DatePay BETWEEN @FromDate AND @ToDate) AS '$Income'
FROM provider
It should be easy to extend to include Writeoffs and InsIncome. After that, can it be put in the query pages please? I know several people asked for something like this quite a while.
Re: monthly production by provider
Posted: Wed Dec 09, 2009 10:20 pm
by V Suite
Jordan, it seems that I cannot test this fully because I have no entries in claimproc. Somehow that causes the TotalProduction and TotalIncome sums to be ?indefinite value? and therefore '0', so I need to leave out "+ $WriteOffs" in the $TotalProduction line, as well as "+ $InsIncome" in the $Total Income line for it to work properly.
Please test it as shown below to see whether it gives the correct results. Thanks.
SET @FromDate='2009-08-01' , @ToDate='2009-08-31';
SELECT ProvNum, (SELECT SUM(ProcFee) FROM procedurelog WHERE procedurelog.ProcStatus=2 AND procedurelog.ProvNum=provider.ProvNum AND procedurelog.procdate BETWEEN @FromDate AND @ToDate) AS $Production,
(SELECT SUM(AdjAmt) FROM adjustment WHERE adjustment.ProvNum=provider.ProvNum AND adjustment.AdjDate BETWEEN @FromDate AND @ToDate) AS $Adjustment,
(SELECT SUM(WriteOff) FROM claimproc WHERE claimproc.Status IN (0, 1, 4) AND claimproc.ProvNum=provider.ProvNum AND claimproc.DateCP BETWEEN @FromDate AND @ToDate) AS $WriteOffs,
(SELECT $Production + $Adjustment + $WriteOffs) AS $TotalProduction,
(SELECT SUM(SplitAmt) FROM paysplit WHERE paysplit.ProvNum=provider.ProvNum AND paysplit.DatePay BETWEEN @FromDate AND @ToDate) AS '$Income',
(SELECT SUM(InsPayAmt) FROM claimproc WHERE claimproc.Status IN (0, 1, 4) AND claimproc.ProvNum=provider.ProvNum AND claimproc.DateCP BETWEEN @FromDate AND @ToDate) AS $InsIncome,
(SELECT $Income + $InsIncome) AS $TotalIncome
FROM provider
Re: monthly production by provider
Posted: Sun Dec 13, 2009 8:10 pm
by V Suite
Jordan,
I'm surprised no one else with associates weighed in on whether this report worked for them.
Did you test it? And does it work properly? It seems fairly slow (5 seconds plus, I suppose because of the multiple SELECTS) but it is accurate, and a time saver for me personally (four dentists in the practice).
Can you implement something like this in the reports - Annual or monthly production by provider, where the providers are selected and the summary sheet is shown? I guess that would not be 6.9, right?
Cheers.
Re: monthly production by provider
Posted: Thu Jan 07, 2010 6:17 am
by V Suite
This query is fairly slow, can anyone advise how to accelerate it please?
Re: monthly production by provider
Posted: Tue Mar 23, 2010 9:26 pm
by V Suite
Jordan,
This is the updated code. By creating the temp tables, it operates much faster (which is the point) and I have fixed the problem which occurred when the claimproc table was empty (such that TotalProduction and TotalIncome did not sum properly). Yay.
$WriteOff is calculated only from insurance WriteOffs.
Nathan, Does this fulfil the requirements for the query for feature request 830?
Cheers.
Code: Select all
DROP TABLE IF EXISTS tmp, tmp1, tmp2, tmp3, tmp4;
SET @FromDate='2010-01-01', @ToDate='2010-12-31';
CREATE TABLE tmp
SELECT ProcFee, ProvNum FROM procedurelog WHERE procedurelog.ProcStatus=2 AND procedurelog.procdate BETWEEN @FromDate AND @ToDate;
CREATE TABLE tmp1
SELECT AdjAmt, ProvNum FROM adjustment WHERE adjustment.AdjDate BETWEEN @FromDate AND @ToDate;
CREATE TABLE tmp2
SELECT SplitAmt, ProvNum FROM paysplit WHERE paysplit.DatePay BETWEEN @FromDate AND @ToDate;
CREATE TABLE tmp3
SELECT WriteOff, ProvNum FROM claimproc WHERE claimproc.Status IN (0, 1, 4) AND claimproc.DateCP BETWEEN @FromDate AND @ToDate;
CREATE TABLE tmp4
SELECT InsPayAmt, ProvNum FROM claimproc WHERE claimproc.Status IN (0, 1, 4) AND claimproc.DateCP BETWEEN @FromDate AND @ToDate;
SELECT ProvNum, (SELECT SUM(ProcFee) FROM tmp WHERE tmp.ProvNum=provider.ProvNum) AS $Production,
(SELECT SUM(AdjAmt) FROM tmp1 WHERE tmp1.ProvNum=provider.ProvNum) AS $Adjustment,
(SELECT SUM(WriteOff) FROM tmp3 WHERE tmp3.ProvNum=provider.ProvNum) AS $WriteOffs,
(SELECT $Production + $Adjustment + IFNULL($WriteOffs,0)) AS $TotalProduction,
(SELECT SUM(SplitAmt) FROM tmp2 WHERE tmp2.ProvNum=provider.ProvNum) AS '$Income',
(SELECT SUM(InsPayAmt) FROM tmp4 WHERE tmp4.ProvNum=provider.ProvNum) AS $InsIncome,
(SELECT $Income + IFNULL($InsIncome,0)) AS $TotalIncome
FROM provider;
DROP TABLE IF EXISTS tmp, tmp1, tmp2, tmp3, tmp4;
Re: monthly production by provider
Posted: Thu Mar 25, 2010 5:21 am
by V Suite
Fixed.
Cheers.