Open Dental Forum

I've been trying to edit the following query and it's almost what I need. I would like to add the patient's subscriber ID but can't find a way to do it. Can anyone help me?

SET @FromDate ='2015-05-01', @ToDate ='2015-05-01';
SET @ProvAbbr ='JBA';
SELECT provider.Abbr, 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 '$EstPlan_',
(SELECT SUM(SplitAmt)
FROM paysplit WHERE patient.PatNum=paysplit.PatNum
AND (Date(DatePay)=(Date(procedurelog.ProcDate)))) AS '$PctePago_',
(SELECT SUM(AdjAmt) FROM adjustment
WHERE patient.PatNum=adjustment.PatNum
AND (Date(AdjDate)=(Date(procedurelog.ProcDate)))) AS '$Ajustes_',
(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 'ComoPago_'
FROM patient
INNER JOIN procedurelog ON patient.PatNum=procedurelog.PatNum
INNER JOIN provider ON provider.ProvNum=procedurelog.ProvNum
WHERE procedurelog.ProcDate BETWEEN @FromDate AND @ToDate
AND procedurelog.ProcStatus=2
AND provider.Abbr LIKE @ProvAbbr
GROUP BY procedurelog.ProcDate,patient.PatNum
ORDER BY procedurelog.ProcDate,patient.LName, patient.FName;

You would have to join the patplan table using patnum (ex: INNER JOIN patplan ON patplan.PatNum=patient.PatNum) then join inssub table using InsSubNum you got from the patplan (ex: INNER JOIN inssub ON inssub.InsSubNum=patplan.InsSubNum). Then you'd want to show the inssub.SubscriberID.

So for instance if you want only people who have a subscriber ID you can simply put in two inner joins one for the patplan and one for the inssub and up at the top add in inssub.SubscriberID in your select statement. If you want everybody regardless, do two left joins.

Perfect, Thank You so much.

I tried to add the insurance carrier to the query, but then I don't get the patients that have no insurance. This is what I have, it will give me provider selected, subscriber ID if it exists, patient name, production for the selected dates, insurance estimate, patient payment, adjustments and how patient paid. Can someone please help me add the insurance carrier if it exists without removing the patients who don't have insurance.

SET @FromDate ='2015-05-01', @ToDate ='2015-05-01';
SET @ProvAbbr ='JBA';
SELECT 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 '$PlanEst_',
(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 '$Adjustments_',
(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 'HowPaid_'
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
WHERE procedurelog.ProcDate BETWEEN @FromDate AND @ToDate
AND procedurelog.ProcStatus=2
AND provider.Abbr LIKE @ProvAbbr
GROUP BY procedurelog.ProcDate,patient.PatNum
ORDER BY procedurelog.ProcDate,patient.LName, patient.FName;

I finally got it. Here's the finished query:

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 '$EstPlan_',
(SELECT SUM(SplitAmt)
FROM paysplit WHERE patient.PatNum=paysplit.PatNum
AND (Date(DatePay)=(Date(procedurelog.ProcDate)))) AS '$PctePago_',
(SELECT SUM(AdjAmt) FROM adjustment
WHERE patient.PatNum=adjustment.PatNum
AND (Date(AdjDate)=(Date(procedurelog.ProcDate)))) AS '$Ajustes_',
(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 'ComoPago_'
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 procedurelog.ProcDate,patient.LName, patient.FName;