Patient, Production, Revenue for date range

[V Suite]

Please see this query which lists all patients with existing balances, along with production, adjustment, total production and revenue in the date range.

Code: Select all

SET @FromDate='2010-01-01', @ToDate='2010-12-31';
SELECT PatNum, Round(DATEDIFF( CURDATE(), Birthdate)/365) AS AGE,
(SELECT SUM(ProcFee) FROM procedurelog WHERE procedurelog.ProcStatus=2 AND procedurelog.PatNum=patient.PatNum AND procedurelog.procdate BETWEEN @FromDate AND @ToDate) AS '$Production', 
(SELECT SUM(AdjAmt) FROM adjustment WHERE adjustment.PatNum=patient.PatNum AND adjustment.AdjDate BETWEEN @FromDate AND @ToDate) AS '$Adjustment',
(SELECT IFNULL($Production, 0) + IFNULL($Adjustment,0)) AS '$TotalProduction',
(SELECT SUM(SplitAmt) FROM paysplit WHERE paysplit.PatNum=patient.PatNum AND paysplit.DatePay BETWEEN @FromDate AND @ToDate) AS '$Revenue', BalTotal, BalOver90, PriProv
FROM patient WHERE PatStatus != 4 AND BalTotal>0 ORDER BY LName,FName;

How do I limit the results to those patients seen in the date range please?

[jordansparks]

Untested, but here's the idea:

Code: Select all

SET @FromDate='2010-01-01', @ToDate='2010-12-31';
SELECT PatNum, Round(DATEDIFF( CURDATE(), Birthdate)/365) AS AGE,
(SELECT SUM(ProcFee) FROM procedurelog WHERE procedurelog.ProcStatus=2 AND procedurelog.PatNum=patient.PatNum AND DATE(procedurelog.procdate) BETWEEN @FromDate AND @ToDate) AS '$Production', 
(SELECT SUM(AdjAmt) FROM adjustment WHERE adjustment.PatNum=patient.PatNum AND adjustment.AdjDate BETWEEN @FromDate AND @ToDate) AS '$Adjustment',
(SELECT IFNULL($Production, 0) + IFNULL($Adjustment,0)) AS '$TotalProduction',
(SELECT SUM(SplitAmt) FROM paysplit WHERE paysplit.PatNum=patient.PatNum AND paysplit.DatePay BETWEEN @FromDate AND @ToDate) AS '$Revenue', BalTotal, BalOver90, PriProv
FROM patient WHERE PatStatus != 4 AND BalTotal>0 
AND EXISTS(SELECT * FROM procedurelog p2 WHERE p2.PatNum=patient.PatNum AND (DATE(p2.ProcDate) BETWEEN @FromDate AND @ToDate) AND p2.ProcStatus=2)
ORDER BY LName,FName;

And you do have to be a little careful with those date ranges. ProcDate has a time component. So you actually want to use Date(procedurelog.ProcDate) anyplace where you would normally use procedurelog.ProcDate, or you will end up with all kinds of problems when comparing because of those times.

[V Suite]

Thanks much Jordan.

And I'll recheck my queries that have date...

Thanks again.