monthly production by provider

[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.

[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

[jordansparks]

That's request 830

[V Suite]

Thanks for the reply. I take it a query will not do it, and it must be programmed in?

[jordansparks]

Oh. Yes, a query would work, but boy would it be big and messy.

[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

[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

[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.

[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

[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.

[V Suite]

This query is fairly slow, can anyone advise how to accelerate it please?

[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;

[V Suite]

Fixed.

Cheers.