Finding most current record when duplicates exist 1

[KXFID]

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.

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:

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

 

[notadba]

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;