Open Dental Forum

I'm looking for a report for a date range and specific codes. I found this query:

/*508 Active patients who have a specific code in treatment plan, with addresses
like 304 with date range*/
/*Query code written/modified: 08/26/2016*/
SET @FromDate='2010-01-01';
SET @ToDate='2010-06-01';
SET @pos=0;
SELECT @pos:=@pos+1 AS COUNT, A.*
FROM (
SELECT pc.ProcCode,
p.LName,
p.FName,
p.Address,
p.Address2,
p.City,
p.State,
p.Zip
FROM patient p
INNER JOIN procedurelog pl ON p.PatNum=pl.PatNum
AND pl.ProcStatus=1 /*treatment planned*/
INNER JOIN procedurecode pc ON pc.CodeNum=pl.CodeNum
WHERE DATE(pl.DateTP) BETWEEN @FromDate AND @ToDate
AND pc.ProcCode IN('D0120') /* NOTE: put as many procedurecodes as you like, in single quotes separated by commas*/
)A;


However, when I enter my codes in the ('D0120') at the very bottom, nothing comes up. They are codes that we made up in our system, for example N1112, so we can track where we are referring patients to (endo, perio, os)
Thanks!

Works. Use this format;

AND pc.ProcCode IN('D0120','D1110','D0210')

drtmz

That's what I was doing but it apparently only does codes that start with D. Do you know of another query for treatment planned codes that I can enter AND do a date range?

Just tried it with 'N2010' a code we use for BW from another office and it picked it up.

drtmz

AND pc.ProcCode IN('N1114') /* NOTE: put as many procedurecodes as you like, in single quotes separated by commas*/
)A;


There is the last little bit of that query. A patient that is scheduled tomorrow has that N1114 code in their treatment plan. Then I changed the dates to reflect this week. I tested the query leaving the D0120 code and it worked just fine. I am only changing what is in the (), other than the date. This is really starting to piss me off!!! I don't have anyone in the office to help me figure this out

So I figured it out Tom! If you're still curious......it will only pull up those codes that were treatment plannED, not ACTIVE treatment. So on Monday when I want to look for my codes for the people that are scheduled this week, my codes won't show since the days haven't happened yet. And the Treatment Finder in OD won't work because those codes are zero codes. Apparently there isn't a report/query that can search a date range and enter specific codes that are active treatment.

You mean scheduled treatment as in the patients have an appointment vs treatment planned as in it it in the treatment plan but no appointment on the books? If this is the case there are variables you can change to reflect this. I think!!

drtmz