Help with complex query

This query is intended to check work history for applicants appling for driving jobs at a trucking company. Most applicant's have a lengthly work history including mulitple times for the same company. select a.beg_dte,a.end_dte,b.employer cname,f.beg_dte rbeg,f.end_dte rend, a.end_dte order_dte,a.beg_dte order_dte2 from app_wrkhst a,employers b, outer ref_dates f where a.id_nbr=<xxxxx> and a.empl_code is not null and a.empl_code=b.empl_code and a.ref_id=f.ref_id union select a.beg_dte,a.end_dte,b.name cname,f.beginning rbeg,f.end rend, a.end_dte order_dte,a.beg_dte order_dte2 from app_wrkhst a,schools b, outer ref_school f where a.id_nbr=<xxxxx> and a.sch_code is not null and a.sch_code=b.sch_code and a.ref_id=f.ref_id union select a.beg_dte,a.end_dte,b.company cname,f.beg_dte rbeg,f.end_dte rend, a.end_dte order_dte,a.beg_dte order_dte2 from app_wrkhst a,app_leased b, outer ref_dates f where a.id_nbr=<xxxxx> and a.leased_id is not null and a.leased_id=b.leased_id and b.ref_id=f.ref_id union select a.beg_dte,a.end_dte,"UNEMPLOYED" cname,today rbeg,today rend, a.end_dte order_dte,a.beg_dte order_dte2 from app_wrkhst a where a.id_nbr=<xxxxx> and a.empl_code is null and a.sch_code is null order by 1 desc into temp worksumm with no log; update worksumm set rend=today where rend is null and rbeg is not null; update worksumm set (order_dte,order_dte2)= (extend(rend,year to month),extend(rbeg,year to month)) where rend is not null and cname<>"UNEMPLOYED"; update worksumm set (rbeg,rend)=(null,null) where cname="UNEMPLOYED"; update worksumm set order_dte=current where order_dte is null; select * from worksumm where abs(date(beg_dte)-date(extend(rbeg, year to month))) not in (select abs(date(beg_dte)- date(extend(rbeg,year to month))) from worksumm where abs(date(beg_dte)-date(extend(rbeg,year to month)))>1 and cname in(select cname from worksumm group by cname having count(*)>2) group by 1 having count(*)>1) or rbeg is null order by order_dte desc,order_dte2 desc This query works great about 95% of the time but the other 5% the results create an invalid work history for an applicant. Example: beg_dte end_dte cname rbeg rend order_dte order_dte2 1994-03 1994-09 PORT CITY TRUCKING INC. 09/30/96 12/12/96 1996-12 1996-09 1996-09 PORT CITY TRUCKING INC. 09/30/96 12/12/96 1996-12 1996-09 1994-03 1994-09 PORT CITY TRUCKING INC. 02/23/94 09/03/94 1994-09 1994-02 1996-09 PORT CITY TRUCKING INC. 02/23/94 09/03/94 1994-09 1994-02 In this instance the ABS for line 1 is 915, line 2 0, line 3 28, line 4 943. lines 2 and 3 should be the only ones returned. Any help will be welcome. -- Joseph Cullipher |E-mail: joseph@cannonexpress.com PO Box 364 |opinions express are those of my own and Springdale, AR 72764 USA |don't necessarily reflect those of my company