How to check if an employee posted an adjustment in the past

For users or potential users.
Post Reply
rhaber123
Posts: 415
Joined: Fri Dec 11, 2009 12:09 pm

How to check if an employee posted an adjustment in the past

Post by rhaber123 » Tue Apr 21, 2015 6:48 pm

Is there a Query regarding an adjustment posted in the past

If an employee kept a check or cash today, and a week later posted an adjustment in the patient account in the past.
Can that be tracked?

Is there a query , during a period of one month, that shows the user name, the date, and time of the adjustment when it was posted in opendental
and that same query shows the date that this adjustment was posted for.
If both dates match, we are ok. If the dates do not match , that should be checked

KevinRossen
Posts: 293
Joined: Mon Apr 22, 2013 8:49 am
Location: Dallas, TX
Contact:

Re: How to check if an employee posted an adjustment in the

Post by KevinRossen » Wed Apr 22, 2015 9:23 pm

I'll whip something up tomorrow.
Kevin Rossen
Office Manager, Rossen Dental
Founder, DivergentDental.com
Image

rhaber123
Posts: 415
Joined: Fri Dec 11, 2009 12:09 pm

Re: How to check if an employee posted an adjustment in the

Post by rhaber123 » Thu Apr 23, 2015 3:41 pm

looking forward to use it. Thank you

User avatar
Hersheydmd
Posts: 700
Joined: Sun May 03, 2009 9:12 pm

Re: How to check if an employee posted an adjustment in the

Post by Hersheydmd » Sat Apr 25, 2015 4:04 pm

I couldn't write one query to fully serve your purpose, but the two below used together should give you what you want.
Use the first query to find adjustments where the date it was entered doesn't match the adjustment date.
Then compare it to the second query to find the user and computer that made the adjustment. The log date in the second query should match the entry date in the first query.
You should also consider setting your security settings so only you can edit adjustments from the past. You would do that by going to Setup; Security; click on the User Group you want to edit; set Adjustment Edit to days newer than 1. Then they will only be able to edit an adjustment made "today".

/*List of Adjustments where the Entry Date doesn't match the Adjustment Date*/
SET @FromDate='2015-01-01', @ToDate='2015-12-31';
SELECT DateEntry, AdjDate, AdjAmt, PatNum, AdjType, AdjNote
FROM adjustment
WHERE DateEntry <> AdjDate
AND DateEntry BETWEEN @FromDate AND @ToDate


/*Audit Trail - Adjustments edited in date range. Modified from query #856 "Audit trail deleted adjustment entries in date range"*/
SET @FromDate='2015-01-01', @ToDate='2015-12-31';
SELECT DATE_FORMAT(sl.LogDateTime,'%m/%d/%Y %h:%i %p') AS 'Log Recorded',
CONCAT(p.LName,', ',p.FName) AS 'Patient',
sl.LogText,
sl.CompName AS 'Computer Edited From',
userod.UserName AS 'User that Edited'
FROM securitylog sl
INNER JOIN userod ON userod.UserNum=sl.UserNum
INNER JOIN patient p ON p.PatNum=sl.PatNum
WHERE sl.PermType=18 /*adjustments edited*/
AND DATE(sl.LogDateTime) BETWEEN @FromDate AND @ToDate
ORDER BY sl.UserNum, sl.LogDateTime
Robert M Hersh DMD, FAGD
Univ. of Penn 1982
Brooklyn, NY 11234
https://www.facebook.com/pages/Robert-M ... 1471599429

KevinRossen
Posts: 293
Joined: Mon Apr 22, 2013 8:49 am
Location: Dallas, TX
Contact:

Re: How to check if an employee posted an adjustment in the

Post by KevinRossen » Sat Apr 25, 2015 10:59 pm

Robert came up with something similar to what I've got. Basically this one query will show you some details of any adjustments posted within a set date range. I haven't been able to make get the date the adjustment was posted for to work right, but this should give you a good amount of info to work with. This will only return the security log on patient adjustments where the date posted and date entered do not match:

Code: Select all

SET @StartDate='2015-04-01', @EndDate='2015-04-30';
SELECT
	u.UserName,
	sl.LogDateTime,
	sl.PatNum,
	sl.LogText
FROM securitylog sl
LEFT JOIN userod u ON sl.UserNum=u.UserNum
WHERE
	DATE(sl.LogDateTime) BETWEEN @StartDate AND @EndDate
	AND sl.PermType IN (17,18)
	AND sl.PatNum IN 
		(
		SELECT a.PatNum
		FROM adjustment a
		WHERE 
			DateEntry<>AdjDate
			AND DateEntry BETWEEN @StartDate AND @EndDate
		);
Kevin Rossen
Office Manager, Rossen Dental
Founder, DivergentDental.com
Image

User avatar
Hersheydmd
Posts: 700
Joined: Sun May 03, 2009 9:12 pm

Re: How to check if an employee posted an adjustment in the

Post by Hersheydmd » Sun Apr 26, 2015 5:49 am

Hi Kevin,
I sometimes copy examples without clearly understanding them.
Can you explain the line:
LEFT JOIN userod u ON sl.UserNum=u.UserNum
Also, how does WHERE DateEntry<>AdjDate work, when DateEntry & AdjDate are fields in the Adjustment table and Adjustment is not in the FROM line? Does that make sense?
Robert M Hersh DMD, FAGD
Univ. of Penn 1982
Brooklyn, NY 11234
https://www.facebook.com/pages/Robert-M ... 1471599429

KevinRossen
Posts: 293
Joined: Mon Apr 22, 2013 8:49 am
Location: Dallas, TX
Contact:

Re: How to check if an employee posted an adjustment in the

Post by KevinRossen » Sun Apr 26, 2015 9:48 pm

Hersheydmd wrote: Can you explain the line:
LEFT JOIN userod u ON sl.UserNum=u.UserNum
All that line is doing is joining the userod table to the securitylog table to make it easier to read in report which user is being identified in the security log. So, instead of having the column be usernum with an entry of 5, you can see the username Robert. Look back up at the third line of the query. Notice how that line starts with "u." and the rest of the lines are "sl."? The u is just shorthand for userod, which is called out by putting the u after userod on the LEFT JOIN line. Just makes it a little easier to type. You could accomplish the same thing by typing userod.UserName, but I like to type less, so I use the aliases/shorthand.
Hersheydmd wrote: Also, how does WHERE DateEntry<>AdjDate work, when DateEntry & AdjDate are fields in the Adjustment table and Adjustment is not in the FROM line? Does that make sense?
What I've done is placed a sub-query withing my main query (not sure if sub-query is a correct technical term, but I think it conveys what's going on best). Notice the opening and closing parentheses on lines 13 & 19? I've placed an entire query within those parentheses to find adjustments entered withing the designated date range on line 1. The only thing this query is doing is returning the PatNum for patients who meet that criteria, but it is very helpful because it limits the number of results returned from the securitylog to only patients who might need to be looked at.
Kevin Rossen
Office Manager, Rossen Dental
Founder, DivergentDental.com
Image

rhaber123
Posts: 415
Joined: Fri Dec 11, 2009 12:09 pm

Re: How to check if an employee posted an adjustment in the

Post by rhaber123 » Mon Apr 27, 2015 5:02 pm

with these 3 queries, I got more than what I need.
Thank You

User avatar
Hersheydmd
Posts: 700
Joined: Sun May 03, 2009 9:12 pm

Re: How to check if an employee posted an adjustment in the

Post by Hersheydmd » Mon Apr 27, 2015 5:20 pm

Kevin,
Pretty cool. Thanks.
Robert M Hersh DMD, FAGD
Univ. of Penn 1982
Brooklyn, NY 11234
https://www.facebook.com/pages/Robert-M ... 1471599429

Post Reply