June 26, 2008 at 9:20 am
I have a table that is updated from a 3rd party database from the payroll company that the client uses.
I am able to update the DB just fine the real issue i have is users not updating the status of employees
the data that is imported is payroll deduction data.
this company rehires a LOT of people,
the payroll company only tracks employees by SSN or at least that is all they give us
so the only way for me to match up the data that is coming from the payroll access db is to match the SSN from the payroll to the SSN
that is in our database.
However the problem with that is if Joe Blow quit 6 months ago and is rehired then when i do the import his deductions will be inserted into the table with his new unique id in our deduction database and his old one.
To avoid this I made the query only import it if the users termination date was null or if there benefit coverage term date was null.
Unfortunately users of this application do not do a very good job of keeping terminated dates etc updated as well as they
should be. I was wondering if i could somehow have the import only apply to the maximum ID of SSN?
since our unique id is automatically generated for the new employee
so for instance joe blow has a ssn of 123456789
he has 2 ID's 6543 his new one
and 3443 this id should have a terminated date, however employees are slacking
and noone has updated that info so if i do the import to the deduction table
two rows for this ssn will be inserted
id payment date
6543 $25 6/26/2008
3443 $25 6/26/2008
here is my current query
insert into mytable(SSN,check_date,Emp_Contri,EmpID,Date_enter,SMC_STC,H_ID,manualentry)
(SELECT convert (s1.[SSN]),s1.[check Date] as ckdate,
s1.[H125 - deduct] as Emp_Contri,MyDB.EmpID,getdate(),'SMC',HI.H_ID,0
FROM
OPENROWSET('Microsoft.Jet.OLEDB.4.0','d:\myfile\access.mdb';
'admin';'',mytable)
as s1 Inner Join Health.dbo.EmployeeInfo as MyDB On mydb.SSN = s1.SSN
Inner Join Health.dbo.EmployeeData as ED On Ed.EmpDat_EmpID= Mydb.EmpID
Inner Join Health.dbo.Health_Info as HI On HI.Hinfo_EmpID = Mydb.EmpID
Where s1.[check date] = @CKDate and ed.Term_Date is null and HI.Terminatio is null)
June 26, 2008 at 10:28 am
instead of inserting directly into your table you can fill a temp table and then just insert the max id for a SSN into the real table.
or it looks like you can just add a distinct to your select.
June 26, 2008 at 10:52 am
looks like the distinct may work, I am curious to know how does it know which empid to insert? does distinct it automatically pick the highest id first?
June 26, 2008 at 10:55 am
Have you considered removing the deduction records as soon as you know John Doe has been terminated?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 26, 2008 at 11:08 am
1. removing the deduction records won't help me since that requires user interaction which is the whole reason for this issue
2. the records need to be kept for financial record purposes etc
June 26, 2008 at 11:23 am
K - I could see the financial records history as a problem.
hmm... I think you're trying to solve a human problem with automation, which will lead to more problems. The bottom line - one way or another - you'd have to make assumptions as to what is going on, which in HR/payroll and poker are REALLY bad things to have. I wouldn't want to have to explain why I assumed incorrectly about anything HR-related. HR stuff is HR's issue - I might "enforce" the rules on the data, but hey make the rules.
I wouldn't attempt to cover over the laziness of others in this case. The data has got to be right. So I'd recommend making it "their" problem: whenever you end up in a scenario where you cann't definitively tell which record SHOULD be active based on the data itself (and not some rule you came up with), send it back to the person who SHOULD have fixed this, and have them fix it. Be nice, but also - copy their manager. Do it enough, and the problem will get fixed.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 26, 2008 at 11:33 am
Matt while i TOTALLY agree with what you said i wish it were that simple their is turnover in personell like you would not believe.
Plus the fact that there are only 2 or 3 people in HR for a company with close to 2000 employees!!! That is half the issue as to why
records are not updated properly.
If I just use distinct in my select statement is that going to match the SSN to the one with the newest ID in my join??
i know this is not the best solution but this place is SOOOOOOOO Far behind the times and as long as their CEO is in place (who is even farther behind the times!!! He has other people check his email for him, if he does that it can be a miracle!!) I don't see much change in the future
June 26, 2008 at 11:42 am
Well then, at very least - set down and have the HR folks give you the rule on what you should do. Again - let them MAKE the rules, and you can run them.
Assume what you described earlier is what they want you to do, I'd set up a bit flag on those deduction rows, called "Active". upon receiving these files from the payroll company, then deactivate those rows that would now be duplicated by the incoming new rows, and then do your insert.
It will be a lot less disruptive than trying to figure out the MaxID that's "active" per employee each time.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 26, 2008 at 11:47 am
Distinct will not work for you, after I looked at your Select closer.
Add this join into your query.
JOIN (SELECT
MAX(EmpID) as EmpId,
SSN
FROM Health.dbo.EmployeeInfo
GROUP BY SSN ) as LastEmpForSSN ON LastEmpForSSN.EmpId= Mydb.EmpID
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply