Timestamp on Payment or PaySplit

[dporter]

I can't find anything that stores the time when a payment was made. I only see dates. Is there another table besides Payment and Paysplit that might have a timestamp? If not, can you add a timestamp to one of these tables?

We have 9 offices using OD now and 35+ providers. I'd like to create some notifications during the day about collections (payments) but need a timestamp to read when the payment was created.

Thanks.

[jsalmon]

I can definitely ask but I wouldn't plan on this change happening any time soon mainly because this type of a change does not directly affect users but instead only improves the code "under the hood".
You might be able to get a rough idea on the time based on the PaymentCreate audit log entries. However, the logs are not associated to the payments in particular so if multiple payments are made on the same day (sounds like your issue) you will not be able to tell for certainty which time goes to which payment.

[dgraffeo]

Hello,
I believe you are correct in that the Payment only has a date of when it is entered and has no hours/minutes information associated with it. Generally we do not keep track of that level of granularity and after researching this issue for you there does not seem to be a way to associate payments with any sort of hour/minute information directly. As for adding this information to the tables, updating or modifying the database is very dangerous and complicated due to its complexity and the way Open Dental interacts with it, and as Jason has said already it may take a while to get changed (If indeed it ever gets changed).

[jsalmon]

As for adding this information to the tables, updating or modifying the database is very dangerous and complicated...

Agreed. I would imagine, if anything, we would simply add a new date time column called something along the lines of DateTStamp thus leaving the old ones alone but adding this extra functionality.

[KevinRossen]

I can't find anything that stores the time when a payment was made. I only see dates. Is there another table besides Payment and Paysplit that might have a timestamp? If not, can you add a timestamp to one of these tables?

We have 9 offices using OD now and 35+ providers. I'd like to create some notifications during the day about collections (payments) but need a timestamp to read when the payment was created.

Thanks.

Here's a basic concept that might be along the lines of what you're looking for. If you're running it in the User Query in Open Dental it will show the names and payment type names, but if you're running it as a MySQL query outside the program it will need a few more tweaks. It's not quite there, but it's a start.

Code: Select all

SET @SetDate='2014-11-13';
SELECT pm.PatNum, pm.PayType, pm.PayAmt, TIME_FORMAT(sl.LogDateTime, '%h:%i %p') AS TIME
FROM payment pm
INNER JOIN securitylog sl ON pm.PatNum=sl.PatNum 
WHERE pm.PayDate=@SetDate AND sl.PermType=15
GROUP BY pm.PayNum;

[tgriswold]

Here's a basic concept that might be along the lines of what you're looking for. If you're running it in the User Query in Open Dental it will show the names and payment type names, but if you're running it as a MySQL query outside the program it will need a few more tweaks. It's not quite there, but it's a start.

Code: Select all

SET @SetDate='2014-11-13';
SELECT pm.PatNum, pm.PayType, pm.PayAmt, TIME_FORMAT(sl.LogDateTime, '%h:%i %p') AS TIME
FROM payment pm
INNER JOIN securitylog sl ON pm.PatNum=sl.PatNum 
WHERE pm.PayDate=@SetDate AND sl.PermType=15
GROUP BY pm.PayNum;

That query sort of works. But as Jason mentioned if there are two payments on the same date for that patient, that query would give incorrect results. This is because it just joined in two audit trail entries into one payment, and you just grouped by payment. So mysql is just picking one datetime at random from those two audit trail entries. Using audit trail entries gets even more complicated if any payments are deleted, or if the payment was edited to have a different paydate then it was initially created with.

I wouldn't recommend trying to link audit trail entries like these to the database entries that created them unless the securitylog entry makes use of the FKey column, which payments don't currently do. Without the direct linkage there are too many factors that can cause you to get an unreliable/incorrect result.

[KevinRossen]

That query sort of works. But as Jason mentioned if there are two payments on the same date for that patient, that query would give incorrect results. This is because it just joined in two audit trail entries into one payment, and you just grouped by payment. So mysql is just picking one datetime at random from those two audit trail entries. Using audit trail entries gets even more complicated if any payments are deleted, or if the payment was edited to have a different paydate then it was initially created with.

I wouldn't recommend trying to link audit trail entries like these to the database entries that created them unless the securitylog entry makes use of the FKey column, which payments don't currently do. Without the direct linkage there are too many factors that can cause you to get an unreliable/incorrect result.

This one is a little better. It shows the first and last time a payment was created. It's not perfect, but imperfect is better than nothing.

Code: Select all

SET @SetDate='2014-11-14';
SELECT pm.PatNum, pm.PayType, pm.PayAmt, TIME_FORMAT(MIN(sl.LogDateTime), '%h:%i %p') AS FirstEntry, TIME_FORMAT(MAX(sl.LogDateTime), '%h:%i %p') AS LastEntry
FROM payment pm
INNER JOIN securitylog sl ON pm.PatNum=sl.PatNum 
WHERE pm.PayDate=@SetDate AND sl.PermType=15 AND DATE(sl.LogDateTime)=@SetDate
GROUP BY pm.PayNum;

[tgriswold]

I would also recommend using payment.DateEntry instead of payment.PayDate since payment.PayDate is user editable and you could technically enter a payment for a past/future date. Payment.DateEntry should always match the audit trail PaymentCreate permissions's date.