pbare
Well Known Member
Hi All,
I hope someone will be able to help me with my query.
I need to write a SQL that will read data from the F08042 table for a certain data item. I need to keep the latest EFTO record and delete the other duplicate records
I have started with the following query
select jwan8, jwdtai, jwhstd, count(jwhstd)
from proddta.f08042
where jwfile = 'F060116'
and jwdtai = 'HMST'
having count(jwhstd) > 1
group by jwan8, jwdtai, jwhstd
order by jwan8
This returns me every Address number that has duplicate records for this data item.
I need to find the greatest EFTO record for each employee group and then delete any other records prior to that EFTO
ie.
AN8 DTAI HSTD EFTO
900085 HMST 3EC 06/24/2011 KEEP
900085 HMST 3EC 06/09/2011 DELETE
900085 HMST 3EC 07/29/2010 DELETE
Can this be done using SQL?
Any thoughts or suggestions would be appreciated.
Thanks
Pam
I hope someone will be able to help me with my query.
I need to write a SQL that will read data from the F08042 table for a certain data item. I need to keep the latest EFTO record and delete the other duplicate records
I have started with the following query
select jwan8, jwdtai, jwhstd, count(jwhstd)
from proddta.f08042
where jwfile = 'F060116'
and jwdtai = 'HMST'
having count(jwhstd) > 1
group by jwan8, jwdtai, jwhstd
order by jwan8
This returns me every Address number that has duplicate records for this data item.
I need to find the greatest EFTO record for each employee group and then delete any other records prior to that EFTO
ie.
AN8 DTAI HSTD EFTO
900085 HMST 3EC 06/24/2011 KEEP
900085 HMST 3EC 06/09/2011 DELETE
900085 HMST 3EC 07/29/2010 DELETE
Can this be done using SQL?
Any thoughts or suggestions would be appreciated.
Thanks
Pam