Help editing query

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:

Help editing query

Post by Jorgebon » Mon May 25, 2015 6:47 am

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

User avatar
dgraffeo
Posts: 147
Joined: Wed Sep 24, 2014 3:19 pm

Re: Help editing query

Post by dgraffeo » Tue May 26, 2015 8:17 am

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.
"To understand what recursion is, you must first understand recursion."

David Graffeo
Open Dental Software
http://www.opendental.com

User avatar
Jorgebon
Posts: 502
Joined: Mon Jun 18, 2007 2:25 pm
Location: Mayaguez, PR
Contact:

Re: Help editing query

Post by Jorgebon » Tue May 26, 2015 10:01 am

Perfect, Thank You so much.
Jorge Bonilla DMD
Open Dental user since May 2005

User avatar
Jorgebon
Posts: 502
Joined: Mon Jun 18, 2007 2:25 pm
Location: Mayaguez, PR
Contact:

Re: Help editing query

Post by Jorgebon » Thu Jun 18, 2015 7:24 pm

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

User avatar
Jorgebon
Posts: 502
Joined: Mon Jun 18, 2007 2:25 pm
Location: Mayaguez, PR
Contact:

Re: Help editing query

Post by Jorgebon » Thu Jun 18, 2015 7:33 pm

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

Post Reply