patient specific data

[d2dental]

if i want to get patient specific data/ report for a specific time period as monthly, yearly or lifetime; with following columns:
-reg no.-First name-middle name-last name-production-total income .

Another report for a specific time period as monthly, yearly or lifetime; with following columns:
Number of patients- where total income top 10% or say above $ 10K - total income between $ 5K-10K- Toatal income bet. % 2K-5K and so on.

Better would be having same query with procedure in place of patients.

Best would be to able to have an option of graphical presentation of data..

thanks and regards..

[Hersheydmd]

Try this. I started with Query #328 and modified it. Let me know if it works.

/*Production and Income for all patients (with adjustments, insurance income and writeoffs)
All by service date (which means the results change as new ins payments are received, except patient payments and adjustments, which is by payment date as there is not always a link between payment and procedure, */
SET @FromDate='' , @ToDate='2020-12-31';
SELECT p.PatNum as 'Patient#', p.FName, p.MiddleI, p.LName,
(SELECT SUM(InsPayAmt) FROM claimproc cp WHERE cp.PatNum=p.PatNum AND cp.Status IN(1,4,7) AND cp.ProcDate BETWEEN @FromDate AND @ToDate) AS '$InsProcPay',
(SELECT SUM(Writeoff) FROM claimproc cp WHERE cp.PatNum=p.PatNum AND cp.Status IN(1,4,7) AND cp.ProcDate BETWEEN @FromDate AND @ToDate) AS '$WriteOff',
(SELECT SUM(SplitAmt) FROM paysplit WHERE p.PatNum=paysplit.PatNum AND (DatePay BETWEEN @FromDate AND @ToDate)) AS '$PatientPay',
(SELECT SUM(AdjAmt) FROM adjustment WHERE p.PatNum=adjustment.PatNum AND (adjdate BETWEEN @FromDate AND @ToDate)) AS '$PatAdj',
SUM(pl.procfee) AS '$Production'
FROM Patient p
INNER JOIN procedurelog pl ON p.PatNum=pl.PatNum AND pl.ProcStatus=2
WHERE pl.ProcDate BETWEEN @FromDate AND @ToDate
GROUP BY p.PatNum
Order BY p.LName;

[d2dental]

Thanks, this query pull out the pt soecific data. After getting in excel sheet other inferences can be presented.