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;
Query subtotals
Query subtotals
Jorge Bonilla DMD
Open Dental user since May 2005
Open Dental user since May 2005
-
- Posts: 293
- Joined: Mon Apr 22, 2013 8:49 am
- Location: Dallas, TX
- Contact:
Re: Query subtotals
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.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?
-
- Posts: 293
- Joined: Mon Apr 22, 2013 8:49 am
- Location: Dallas, TX
- Contact:
Re: Query subtotals
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;