New patients reporting - DateFirstVisit

For complex topics that regular users would not be interested in. For power users and database administrators.
Post Reply
Mifa
Posts: 141
Joined: Wed Nov 21, 2007 6:52 pm
Location: Saint-Bruno, QC, Canada
Contact:

New patients reporting - DateFirstVisit

Post by Mifa » Tue Jun 11, 2013 12:03 pm

I'd like to get some advice on a situation I cannot figure out. A few years ago we bought another clinic and imported/merged their patients with ours in OD. In order to avoid seeing "their" patients being counted as NP in the new patients report on their first appointment at "our" clinic, I have developed a custom query that omits those patients based on their patnum. It's been working just fine on that matter, but we've noticed that some other patients who show on the regular OD report are omitted from my custom NP report. Here are the two recent examples that are puzzling us:

Patient 1 is added in OD on April 22, 2013 and her first appointment is set to May 26, 2013. She does not show up on my custom May NP report. In the patient table, the DateFirstVisit field shows April 22, 2013. I've checked the procedurelog and appointment tables, and there is no trace of anything for April 22, and all completed procedure and appointment are on May 26. The regular audit trail doesn't show any (deleted) appointment/procedure (mistakenly) marked complete on April 22.

Patient 2 is added in OD on May 22, 2013 with a first appointment created for this same date then 3 minutes later according to the audit trail a recall appointment is scheduled in the agenda for November 28, 2013. The first appointment and its procedures are set complete on May 22, 2013. The patient's DateFirstVisit shows November 28 in the patient table. Additionally, both appointments (in May and November) are showing a NP next to her name on the agenda.

Here is my question: how is the DateFirstVisit created in the first place?

I've noticed that the regular NP report uses "DateFirstProc" (not a standard database field but it is also referenced in query 490 in the query examples) instead of DateFirstVisit, it would be nice if both would return the same value.

Thanks.

Mifa

Post Reply