×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Finding most current record when duplicates exist

Finding most current record when duplicates exist

Finding most current record when duplicates exist

(OP)
Good day,
I have three tables in our payroll system that I need to select against. I either get duplicates of all employee records for that operator or only those that have no duplicate records at all.

CODE

Table1:  Operators to department (A)
oprid    deptid
10001    DEPT1
10001    DEPT2
10002    DEPT2
10002    DEPT3

Table2: Department to employee "B"
deptid    Emplid
DEPT1      E1
DEPT2      E2
DEPT2      E3
DEPT2      E4
DEPT3      E5

Table3: Employee To Employee Name "C"
emplid    Name            effdt        effseq
E1    John Smith        2001-01-01      1
E2    Jane Doe          2000-01-05      1
E2    Jane Smith        2007-09-15      1
E2    Jane Doe-Smith    2007-09-15      2
E3    Dudley Doright    2007-01-11      1
E4    Mary Contrary     2000-05-12      1
E5    Kevin Nivek       1999-02-22      1
I want to select only the most recent record for all employees who work in a department which is supervised by operator id 10001.  Jane has three total records and two records against her name for the same date so the sequence indicates the most current.
The result set would be:

CODE

Emplid        Name           Effdt      effseq
E2      Jane Doe-Smith   2007-09-15     2
E3      Dudley Doright   2007-01-11     1
E4      Mary Contrary    2000-05-12     1
[\code]
My attempt (among many others)
[code]
select B.emplid, B.effdt, B.effseq, A.deptid, C.name
from   A, B, C
where A.oprid="10001"
and   B.deptid=A.deptid
and   B.effdt =
      (select max(SS.effdt) from B SS
       where SS.emplid = B.emplid
       and   SS.empl_rcd=0
       Group by B.emplid
       )
and   B.effseq =
      (select max(SS.effseq) from B SS
       where SS.emplid = B.emplid
       and   SS.empl_rcd=0
       Group by B.emplid
       )
and C.emplid=B.emplid;
Returns duplicate records but none of Jane's (E2).  


TIA

RE: Finding most current record when duplicates exist

It looks like you are not correctly evaluating the max sequence for a given effective date.  Try something like:

select B.emplid, B.effdt, B.effseq, A.deptid, C.name
from   A, B, C
where A.oprid="10001"
and   B.deptid=A.deptid
and   B.effdt =
      (select max(SS.effdt) from B SS
       where SS.emplid = B.emplid
       and   SS.empl_rcd=0
       and SS.efft <= current date (change for your DBMS) )
and   B.effseq =
      (select max(SS.effseq) from B SS_1
       where SS_1.emplid = B.emplid
       and   SS_1.empl_rcd=0
       and SS_1.effdt = B.effdt
       )
and C.emplid=B.emplid;

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close