Query 1238

For users or potential users.
Post Reply
Erik@AD
Posts: 15
Joined: Thu Jun 09, 2016 12:52 pm

Query 1238

Post by Erik@AD » Wed Sep 20, 2017 12:32 pm

Is it easy to add First and Last Names to the results of this query (1238)?
I am asking because 1238 produces numbers that do not match up with the "built-in" New Patient report and I want to cross check the results.

Thanks,

-Erik

Tom Zaccaria
Posts: 353
Joined: Mon Feb 25, 2008 3:09 am

Re: Query 1238

Post by Tom Zaccaria » Wed Sep 20, 2017 1:20 pm

Yes. You would need a completely different query.

drtmz

Tom Zaccaria
Posts: 353
Joined: Mon Feb 25, 2008 3:09 am

Re: Query 1238

Post by Tom Zaccaria » Wed Sep 20, 2017 1:23 pm

Try this one

/*972 New patients with first appointment in date range*/
/*Query code written/modified: 05/05/2014*/
SET @FromDate='2014-04-01', @ToDate='2014-05-05';
SELECT DISTINCT CONCAT(p.FName, ' ', p.LName) AS 'Name',
DATE_FORMAT(a.AptDateTime, '%m/%d/%Y %h:%i %p') AS 'Appointment',
prd.Abbr AS 'Dentist',
prh.Abbr AS 'Hygienist',
a.Op
FROM patient p
INNER JOIN appointment a ON a.PatNum=p.PatNum
AND DATE(a.AptDateTime) BETWEEN @FromDate AND @ToDate
AND a.AptStatus IN (1,2,4) /*Sched, Complete, ASAP*/
LEFT JOIN provider prd ON prd.ProvNum=a.ProvNum
LEFT JOIN provider prh ON prh.ProvNum=a.ProvHyg
INNER JOIN (
SELECT a.PatNum,DATE(MIN(a.AptDateTime)) AS 'FirstApptDate'
FROM appointment a
WHERE a.AptStatus IN (1,2,4) /*Sched, Complete, ASAP*/
GROUP BY a.PatNum
) firstap ON firstap.PatNum=p.PatNum
AND firstap.FirstApptDate BETWEEN @FromDate AND @ToDate
AND firstap.FirstApptDate=DATE(a.AptDateTime)
ORDER BY p.LName,p.FName,a.AptNum

drtmz

Erik@AD
Posts: 15
Joined: Thu Jun 09, 2016 12:52 pm

Re: Query 1238

Post by Erik@AD » Wed Sep 20, 2017 2:01 pm

Thanks for replying Tom.

972 and 1238 both produce different results, even after removing dupes from 972. 972 produces the same results as the "built-in".
So I assume 1238 is inaccurate?

-Erik

User avatar
cmcgehee
Posts: 711
Joined: Tue Aug 25, 2015 5:06 pm
Location: Salem, Oregon

Re: Query 1238

Post by cmcgehee » Wed Sep 20, 2017 3:01 pm

It looks like 1238 counts patients whose first procedure was D9986 (broken appointment) while the built-in New Patients report does not count them. That is likely why these two do not match for you.
Chris McGehee
Open Dental Software
http://www.opendental.com

Erik@AD
Posts: 15
Joined: Thu Jun 09, 2016 12:52 pm

Re: Query 1238

Post by Erik@AD » Thu Sep 21, 2017 7:55 am

Thanks Chris, that would make sense.

Post Reply