help with query

[spolevoy]

posted on DT previously

Coming up to that time again - end of year letters and postcards.
My first year-end with OD.
Previously, with Dentrix, we'd run overdue recall report for patients with insurance, remind them of unused funds; and same for patients without insurance, remind them of flex benefits and next year planning.
there's query #384 that's close to what we need.


/*384 List of patients with addresses past due for recall of type prophy or perio
with recall type with insurance or without insurance with NO scheduled apt*/
SET @FromDate='2005-10-01' , @ToDate='2009-12-31';
SET @pos=0;
SELECT @pos:=@pos+1 AS 'Count', rt.description,r.DateDue, p.PatNum, p.Address, p.Address2, p.City, p.State, p.ZIP FROM recall r
INNER JOIN recalltype rt ON r.recalltypenum = rt.recalltypenum
INNER JOIN patient p ON p.PatNum=r.PatNum
LEFT JOIN appointment a ON a.PatNum=p.PatNum AND a.AptStatus=1
WHERE p.patstatus = 0 AND (DATE(r.datedue) BETWEEN @FromDate AND @ToDate) AND p.HasIns='I' /*has insurance, change to <>'I' for the uninsured*/
AND (rt.Description LIKE('%perio%') OR rt.Description LIKE('%prophy%')) AND NOT ISNULL(r.DateDue)
AND ISNULL(a.AptNum)/*no sched apt*/ ;


Adding p.Email for email list gets me real close, but I'd love to have the patient's Insurance name and benefits remaining.

I don't think recall and patient tables have CarrierName field, because p.CarrierName and r.CarrierName return errors.

I see this syntax for benefits remaining -- how do I add it into existing query?

SELECT patient.PatNum,tempannualmax.AnnualMax AS $AnnualMax,tempused.AmtUsed AS $AmountUsed,
(CASE WHEN ISNULL(tempused.AmtUsed) THEN (tempannualmax.AnnualMax) ELSE (tempannualmax.AnnualMax-tempused.AmtUsed) END) AS $AmtRemaining,
FROM patient

Thanks so much!

steve

[Arna]

Instead of running a query, how about using the Treatment Finder Report? Under Reports > Treatment Finder (Right hand Lists).

The treatment finder was built specifically to find a list of patients who have treatment planned procedures that are not scheduled and insurance remaining.

You have the ability to include patients without insurance, include patients who have upcoming appointments, apply certain filters (such as Patient Provider, Billing Type, Insurance Remaining over a certain amount, specific codes etc.) and create a list of patients who could be contacted.
From that list, you can create labels or even generate a custom letter indicating the year is coming to a close and they have insurance that can be applied to their outstanding treatment plans. The letter will import the patient information and generate a personalized letter for each patient on the list. You can also export the list to file if you need to work with it outside of Open Dental.

You can also run your regular recall reports (just change the due dates) to see which patients are due close to the end of the year to get an announcement out them also.

We would be happy to show you how to use these reports, please contact us for a run through.

[spolevoy]

Arna,
That's a great report - and would've given me exactly what I needed, if I were looking for incomplete treatments.
I'm actually looking for overdue recalls, with and without insurance, and their benefits.
Using the treatment finder will omit all the people that are due for recall but have no treatment planned.

steve

[Arna]

I will email you.

[spolevoy]

Arna, this is awesome -just what I needed!!!
Slight problem though - for patients not seen in 2013, $InsLeft comes out as zero, when it should be full benefits...and some of those who have been seen have negative amounts. Any idea why?

[Arna]

Yes, That query is a little incomplete. I have another that would be better. I am actually going to remove that post and will email you the other query, just so fellow forum users don't get confused by this thread.

[rhaber123]

Is it possible to add/post this query on the "Open dental query examples" webpage.
http://opendentalsoft.com:1942/ODQueryL ... yList.aspx
and let us know what query number is it posted under, that will be appreciated. We all need it every year.

if possible, can you email it to me.

THANK YOU
rhaber123 at hotmail dot com

[babysilvertooth]

Can someone post what query this topic is using?

Thanks