Query 1238

[Erik@AD]

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]

Yes. You would need a completely different query.

drtmz

[Tom Zaccaria]

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]

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

[cmcgehee]

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.

[Erik@AD]

Thanks Chris, that would make sense.