Open Dental Forum

I have the following query that will give me a report of the patients that where seen on a given day for a given provider, the production, insurance portion, patient portion and type of payment. It is ordered by insurance carriers. The date and provider are set in the first two lines. Does anyone know how to add subtotals for each carrier and totals at the bottom?

SET @FromDate ='2015-05-01', @ToDate ='2015-05-01';
SET @ProvAbbr ='JBA';
SELECT carrier.carriername, provider.Abbr, inssub.SubscriberID, patient.PatNum, SUM(procedurelog.procfee) AS '$Produc_',
(SELECT SUM(InsPayEst)
FROM claim
WHERE claim.PatNum=patient.PatNum
AND (Date(claim.DateService)=Date(procedurelog.ProcDate))) AS '$EstIns_',
(SELECT SUM(SplitAmt)
FROM paysplit WHERE patient.PatNum=paysplit.PatNum
AND (Date(DatePay)=(Date(procedurelog.ProcDate)))) AS '$Patpay_',
(SELECT SUM(AdjAmt) FROM adjustment
WHERE patient.PatNum=adjustment.PatNum
AND (Date(AdjDate)=(Date(procedurelog.ProcDate)))) AS '$Adjusted_',
(SELECT GROUP_CONCAT(DISTINCT ItemName)
FROM paysplit
INNER JOIN payment ON paysplit.PayNum=payment.PayNum
INNER JOIN definition ON payment.PayType=definition.DefNum
WHERE patient.PatNum=paysplit.PatNum
AND (Date(DatePay)=(Date(procedurelog.ProcDate)))) AS 'PayType_'
FROM patient
INNER JOIN procedurelog ON patient.PatNum=procedurelog.PatNum
INNER JOIN provider ON provider.ProvNum=procedurelog.ProvNum
LEFT JOIN patplan ON patplan.PatNum=patient.PatNum
LEFT JOIN inssub ON inssub.InsSubNum=patplan.InsSubNum
LEFT JOIN insplan ON insplan.PlanNum=inssub.PlanNum
LEFT JOIN carrier ON carrier.CarrierNum=insplan.CarrierNum
WHERE procedurelog.ProcDate BETWEEN @FromDate AND @ToDate
AND procedurelog.ProcStatus=2
AND provider.Abbr LIKE @ProvAbbr
GROUP BY procedurelog.ProcDate,patient.PatNum
ORDER BY carrier.carriername,patient.LName, patient.FName;

Jorgebon wrote:I have the following query that will give me a report of the patients that where seen on a given day for a given provider, the production, insurance portion, patient portion and type of payment. It is ordered by insurance carriers. The date and provider are set in the first two lines. Does anyone know how to add subtotals for each carrier and totals at the bottom?

Do you need each patient's name listed? Right now, the query is grouped by patient. You could remove the PatNum column and group by carrier.

Here's a slightly modified version. Click Print Preview and it will show totals.