Open Dental Forum

Hello Everybody,

I need a little help ..... with this Time-Tracker Query. What's Time Tracker? This query tells us what time a given patient was entered at the FD (Front Desk), how long they have been hanging around (sometimes hours), then the time span spent within any given operatory (CompName).

Everyone in this office uses one password. Why? There's no point in wasting time logging in, then logging out, then logging back in. Yes, we lose some accountability, but we gain lots in productivity.

So this query tells me a great deal about how and how long any given patient is being treated.

Now I would like to add in what dental insurance is attached to the given patient. Why? I can understand spending extra care with our outstanding patients with or without outstanding dental insurance. I cannot understand spending too much time on some of our Cheese-O-Rama dental insurance patients. We want to treat everyone kindly, quickly, and efficiently

Of course, I tell our doctors to, "Spend time with patients," but do not waste time on patients." That is key.

Second, we are known as, "Minimally invasive dentists. We have one standard of care."

What is not said but is always on my mind is that "one standard of care is predicated upon one standard of payment."

This is not the case with our inner-city, half welfare, half nice insurance patient base. But hey! I am cool with that. During the 9 years of operation, we have attracted over 24,000 new, unique patients. Almost all of them are referred to us.

We do not have those guys in orange jackets hawking our wares outside the office. We do not pass out the little cards with the "marketers" name (maybe Hiram) scribbled in the corner. That suggests an illegal pay-off to Hiram. Please refer to Stark I and Stark II federal law.

Our competitors two blocks from here do. So this explains my need for maximum efficiency and my plea for your help!

So with no further ado, here's my little query:

select * from securitylog
where DATE(LogDateTime)>='2015-08-21'
AND (UserNum !='10' OR UserNum !='13' OR UserNum !='14' OR
UserNum !='16' OR UserNum !='18') AND
PatNum !='0' AND
PermType != '26'
ORDER BY PatNum, DATE (LogDateTime) DESC;

We need to include a field for dental insurance.

I thank everybody in advance for your help. I am a beginner with queries so I hope to learn a little from your kind assistance.


Regards,


Joel

Alright, so it's understandable that you're having issues getting the insurance information for patients as it requires a bit of a run-around to get at. First, looking through our database documentation (located at: http://opendental.com/OpenDentalDocumentation15-3.xml) you can see that the insurance carrier information is located in the table "carrier". CarrierNum is the primary key of this table so CarrierNum is what we would reference anywhere else we want to use carrier information. By doing Find in your web browser for CarrierNum you can see it's used in a few places; claimpayment table, etrans table, and insplan table. The first two don't make any sense as they don't have much to do with insurance plans, but the insplan table on the other hand does. The insplan table has a primary key named PlanNum so then you'd want to do another Find for PlanNum and see where it's used (but there's a hint in the table description saying that subscribers can share insplans by using the inssub table). When we look at the inssub table we see that it goes to the insplan table we were just looking at, but also has a foreign key to the patient's PatNum. So, with all this in mind we work backwards and the linking that needs to happen is first patient.PatNum -> inssub.PatNum (since you have patient already), then inssub.PlanNum -> insplan.PlanNum, then insplan.CarrierNum -> carrier.CarrierNum which would get you the carrier's description.

So now that we know what tables we need to go through to get the carrier information we have to determine what kind of 'joins' we want to do, either LEFT joins or INNER joins. LEFT joins means that if a patient doesn't have an insurance plan that the patient will still show up in the results. INNER join means that if the patient doesn't have an insurance plan the patient will not show up in the results. I assume that you DO want them to show up, so it would end up as something like this:

SELECT securitylog.*, carrier.CarrierName
FROM securitylog
LEFT JOIN inssub ON securitylog.PatNum=inssub.Subscriber
LEFT JOIN insplan ON inssub.PlanNum=insplan.PlanNum
LEFT JOIN carrier ON insplan.CarrierNum=carrier.CarrierNum
WHERE DATE(LogDateTime)>='2015-08-21'
AND (UserNum !='10' OR UserNum !='13' OR UserNum !='14' OR
UserNum !='16' OR UserNum !='18')
AND securitylog.PatNum !='0'
AND PermType != '26'
ORDER BY securitylog.PatNum, DATE (LogDateTime) DESC;

Hopefully this helps and gives you some insight into how the queries are written!

Edit: Found some errors in the query, namely that the inssub links PatNum with Subscriber (go figure), then some typos and a couple aliases that needed to be put in there so it knew what table to look at for PatNum.

Thank you so much David, for your clear and concise reply.
You are a gentleman and a scholar. Thanks again ..... Joel

JOEEEEEEEEEEEEEEELLLLLLYYYYYY!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

September 13, 2015

Hello Justin, David, and others,

Good to touch base with you. Its been 10 plus years, Justin! We hope you are doing well and have great success with your work.

Quick update for David Graffeo:

I used your last explanation about INNER JOIN and LEFT JOIN and I further reviewed MySQL at w3.schools.com.

However, your explanation gave me nice insight how to modify this query to include both insurance-covered patients and private patients. With INNER JOIN it returns only the intersection, that is, without private patients. Changing to LEFT JOIN increases the subset to include all.

I changed INNER JOIN to LEFT JOIN and voila! Success.

Thanks again, David. Your explanation is truly appreciated.

As a practical matter, we now have more control over which patient population we may first choose to send reminders.
Any other ideas about including other fields to further refine recall lists is greatly appreciated.

Regards,

Joel M. Eichen


SELECT p.FName, p.LName, p.Address, p.Address2, p.City, p.State, p.Zip, c.CarrierName, MAX(apt.AptDateTime) AS 'LastApt', MIN(apt.AptDateTime) AS 'FirstApt'
FROM appointment apt
INNER JOIN patient p ON apt.PatNum = p.PatNum AND apt.AptStatus in (1, 2) AND p.PatStatus = 0
LEFT JOIN patplan pp ON p.PatNum = pp.PatNum AND pp.Ordinal = 1
LEFT JOIN inssub iss ON pp.InsSubNum = iss.InsSubNum
LEFT JOIN insplan ip ON iss.PlanNum = ip.PlanNum
LEFT JOIN carrier c ON ip.CarrierNum = c.CarrierNum
GROUP BY apt.PatNum
ORDER BY c.CarrierName, LastApt DESC

Awesome! You're welcome for the explanation, I'm glad it helped. I'm not sure what other fields you would want to add, but as long as you have a way to "daisy chain" the tables together you can filter things with a WHERE clause placed right before the GROUP BY statement for more specific results. For example, you could put a WHERE clause that says something like WHERE p.FName LIKE '%David%' which would get all entries like it has been, but then only show results for patients that have a first name like David.