Query 237 - Annual Production

[V Suite]

Does not work as is. Fix: Add 'SET' to start of fourth line before "@FromDate='2005-01-01' , @ToDate='2005-12-31';"

In addition it does not work properly if the claimproc Table is empty, in that the TotalProduction column is zeroed as is the TotalIncome column, to match the values of $WriteOff and $InsIncome - whereas Query #266 works.

How to modify #237 to work even if claimproc is empty please?

Nathan?

[nathansparks]

ns/updated, thank you for your keen eye, let me know if there is still an issue


/*Annual production and income report
For all providers*/
DROP TABLE IF EXISTS t1,t2;
SET @FromDate='2010-01-01' , @ToDate='2010-12-31';
CREATE TABLE t1(
Month int NOT NULL,
$Production double NOT NULL,
$Adjustments double NOT NULL,
$WriteOff double NOT NULL,
$TotProd double NOT NULL,
$PatIncome double NOT NULL,
$InsIncome double NOT NULL,
$TotIncome double NOT NULL);
/*Prod*/
INSERT INTO t1(Month,$Production)
SELECT MONTH(pl.ProcDate) AS 'Month',
SUM(pl.procfee) AS '$Production'
FROM procedurelog pl WHERE pl.ProcStatus=2 AND pl.ProcDate BETWEEN @FromDate AND @ToDate
GROUP BY MONTH(pl.ProcDate);
/*Adj*/
CREATE TABLE t2
SELECT MONTH(a.AdjDate) AS 'Month',
SUM(a.AdjAmt) AS 'Adjustments' FROM adjustment a
WHERE a.AdjDate BETWEEN @FromDate AND @ToDate
GROUP BY MONTH(a.AdjDate);
UPDATE t1,t2 SET t1.$Adjustments=t2.Adjustments WHERE t1.Month=t2.Month;
DROP TABLE IF EXISTS t2;
/*PatInc*/
CREATE TABLE t2
SELECT MONTH(pp.DatePay) AS 'Month',
SUM(pp.SplitAmt) AS 'PatIncome' FROM paysplit pp
WHERE pp.DatePay BETWEEN @FromDate AND @ToDate
GROUP BY MONTH(pp.DatePay);
UPDATE t1,t2 SET t1.$PatIncome=t2.PatIncome WHERE t1.Month=t2.Month;
DROP TABLE IF EXISTS t2;
/*InsIncome*/
CREATE TABLE t2
SELECT MONTH(cp.DateCP) AS 'Month',
SUM(cp.InsPayAmt) AS 'InsIncome',
SUM(cp.WriteOff) AS 'WriteOff'
FROM claim c
INNER JOIN claimproc cp ON c.ClaimNum=cp.ClaimNum
WHERE cp.DateCP BETWEEN @FromDate AND @ToDate /*vs c.DateReceived*/
GROUP BY MONTH(cp.DateCP);
UPDATE t1 LEFT JOIN t2 ON t1.Month=t2.Month
SET t1.$InsIncome=t2.InsIncome,
t1.$WriteOff=-t2.WriteOff,
t1.$TotProd=t1.$Production+t1.$Adjustments-IFNULL(t2.WriteOff,0),
t1.$TotIncome=IFNULL(t2.InsIncome,0)+t1.$PatIncome;
DROP TABLE IF EXISTS t2;
SELECT * FROM t1;
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;

[V Suite]

Nathan

Thanks for the prompt reply.

I have used IFNULL to 'fix' the same issue in the two queries below as well. Are they useful for the query example page too (as well as for feature request 830, IIRC). Thanks.

viewtopic.php?f=1&t=379
viewtopic.php?f=1&t=2483