June 25, 2012 at 10:10 am
I have this query that produces duplicate employees. They are in different companies (orglevel2) but same person.
I need to check for duplicate (first and last name) and if it is a duplicate keep only the record with the latest date.
WITH Max_Check_Date
(Org2Code
,Empno
,PayDate)
AS
(select prgorglvl2
,eecempno
,max(prgpaydate) as paydate
from empcomp ec
inner join payreg pr
on eeceeid = prgeeid
where ec.eecemplstatus in ('A','L')
and prgcheckamt + prgddamt <> 0
group by prgorglvl2
,eecempno
)
select prgorglvl2
,eepnamefirst
,eepnamelast
,eepnamemiddle
,eecempno
,prgpaydate
,sum(prgcheckamt) Chk_Amt
,sum(prgddamt) DDA_Amt
,case
when sum(prgcheckamt) > 0 and sum(prgddamt) = 0
then 'CHK'
when sum(prgddamt) > 0 and sum(prgcheckamt) = 0
then 'DDA'
else 'BOTH'
end
from emppers ep
inner join empcomp ec
on eepeeid = eeceeid
inner join payreg pr
on eeceeid = prgeeid
inner join Max_Check_Date cte
on cte.empno = pr.prgempno and
cte.paydate = pr.prgpaydate
where ec.eecemplstatus in ('A','L')
and prgcheckamt + prgddamt <> 0
and EepNameLast like '%kaiser'
group by prgorglvl2
,eepnamefirst
,eepnamelast
,eepnamemiddle
,eecempno
,prgpaydate
June 25, 2012 at 10:39 am
With over 500 points I guess you do know...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 25, 2012 at 10:47 am
I was just trying to figure out if there was a question.
June 25, 2012 at 10:59 am
Sorry.
Is there a way to eliminate the duplicate Employees, taking only the latest prgpaydate in the CTE?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply