Open Dental Forum

I am trying to create a list of active accounts which currently have a particular insurance carrier. Unfortunately my query skills aren't good, and the best I can do is (from the query examples) a list of all patients (active and inactive) who have EVER been associated with that carrier. If anyone is willing to help improve my query, I'd be very grateful. My goal is to generate a list of guarantor names and addresses so that I can send a letter to each household. An amazing bonus would be if even active patients could be filtered further by having an option to exclude people not seen since a certain date. Here's what I've got right now:

/*26*/ SELECT c.CarrierName,p.*
FROM patient p
INNER JOIN inssub iss ON iss.Subscriber=p.PatNum
INNER JOIN insplan ip ON ip.PlanNum=iss.PlanNum
INNER JOIN carrier c ON ip.CarrierNum=c.CarrierNum
WHERE c.CarrierName LIKE '%BCBS%'

Thank you all!

This will get you the guarantor information.
drtmz

~~~~~~~~~~~~~~~~~

/*118 Mailing list of guarantors of patients with a particular carrier, unlike some others, returns the guarantors of all patients with plan, not the subscriber, also if you drop the plan it goes off the list*/

SET @CarrierName='%Delta%'; /*Enter partial carrier name between %%, or leave blank to run for all*/

/*--------------------DO NOT MODIFY BELOW THIS LINE--------------------*/
/*Query code written/modified: 05/13/2022:TylerT*/

SELECT
DISTINCTROW gu.Salutation
,gu.LName
,gu.FName
,gu.Address
,gu.Address2
,gu.City
,gu.State
,gu.Zip
,gu.Email
,gu.Birthdate
,ib.DateEffective
,ca.CarrierName
FROM patient p
INNER JOIN patplan pp
ON pp.PatNum = p.PatNum
INNER JOIN inssub ib
ON ib.InsSubNum = pp.InsSubNum
INNER JOIN insplan ip
ON ip.PlanNum = ib.PlanNum
INNER JOIN carrier ca
ON ca.CarrierNum = ip.CarrierNum
INNER JOIN patient gu
ON gu.PatNum = p.Guarantor
WHERE ca.CarrierName LIKE @CarrierName
ORDER BY ca.CarrierName, gu.LName;

Thank you, Tom!

Anything I could add to this to filter out inactive patients, or to confine the list to patients who have been seen in the office within the last 24 months?