Query subtotals

For complex topics that regular users would not be interested in. For power users and database administrators.
Post Reply
User avatar
Jorgebon
Posts: 502
Joined: Mon Jun 18, 2007 2:25 pm
Location: Mayaguez, PR
Contact:

Query subtotals

Post by Jorgebon » Sat Jun 27, 2015 9:14 am

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;
Jorge Bonilla DMD
Open Dental user since May 2005

KevinRossen
Posts: 293
Joined: Mon Apr 22, 2013 8:49 am
Location: Dallas, TX
Contact:

Re: Query subtotals

Post by KevinRossen » Mon Jun 29, 2015 12:02 pm

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.
Kevin Rossen
Office Manager, Rossen Dental
Founder, DivergentDental.com
Image

KevinRossen
Posts: 293
Joined: Mon Apr 22, 2013 8:49 am
Location: Dallas, TX
Contact:

Re: Query subtotals

Post by KevinRossen » Mon Jun 29, 2015 12:09 pm

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

Code: Select all

SET
	@FromDate ='2015-06-29',
	@ToDate ='2015-06-29',
	@ProvAbbr ='%%'; -- For specific provider, change %% to provider abbreviation
SELECT 
	ca.carriername,
	pv.Abbr,
	SUM(pl.procfee) AS '$Produc_',
	(SELECT SUM(InsPayEst) FROM claim WHERE claim.PatNum=pt.PatNum	AND (Date(claim.DateService)=Date(pl.ProcDate))) AS '$EstIns_',
	(SELECT SUM(SplitAmt) FROM paysplit WHERE pt.PatNum=paysplit.PatNum AND (Date(DatePay)=(Date(pl.ProcDate)))) AS '$Patpay_',
	(SELECT SUM(AdjAmt) FROM adjustment WHERE pt.PatNum=adjustment.PatNum	AND (Date(AdjDate)=(Date(pl.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 pt.PatNum=paysplit.PatNum AND (Date(DatePay)=(Date(pl.ProcDate)))) AS 'PayType_'
FROM patient pt
INNER JOIN procedurelog pl ON pt.PatNum=pl.PatNum
INNER JOIN provider pv ON pv.ProvNum=pl.ProvNum
LEFT JOIN patplan ON patplan.PatNum=pt.PatNum
LEFT JOIN inssub ON inssub.InsSubNum=patplan.InsSubNum
LEFT JOIN insplan ON insplan.PlanNum=inssub.PlanNum
LEFT JOIN carrier ca ON ca.CarrierNum=insplan.CarrierNum
WHERE 
	pl.ProcDate BETWEEN @FromDate AND @ToDate
	AND pl.ProcStatus=2
	AND pv.Abbr LIKE @ProvAbbr
GROUP BY 
	ca.carriername, PayType_
ORDER BY 
	ca.carriername;
Kevin Rossen
Office Manager, Rossen Dental
Founder, DivergentDental.com
Image

Post Reply