return to PRS Technologies website


dupe_costs3.sql
--############################################################################## -- -- Module: dupe_costs3.sql -- Author: Peter R. Schmidt -- Description: An simple sql scripts to list (and delete) duplicate costs -- Note use of batch numbers below -- -- Change Log -- -- Date Name Description................. -- 07/12/01 Peter R. Schmidt Start Program -- --############################################################################# -- drop table temp1; -- isolate costs suspected of being duplicates select * from cost where cbatch between 59461 and 59466 --and cdisbdt <= "3/20/2001" into temp temp1 with no log; -- get total dollars of dupes select count(*),sum(c2.camount) from temp1 c2, cost c1 where c2.cbatch between 59461 and 59466 and c2.cindex <> c1.cindex and c2.cbatch <> c1.cbatch and c2.cmatter = c1.cmatter and c2.cdisbdt = c1.cdisbdt and c2.ccode = c1.ccode and c2.ctk = c1.ctk and c2.cloc = c1.cloc and c2.camount = c1.camount; -- get breakdown of dupes by date and amount select c2.cdisbdt,count(*),sum(c2.camount) from temp1 c2, cost c1 where c2.cbatch between 59461 and 59466 and c2.cindex <> c1.cindex and c2.cbatch <> c1.cbatch and c2.cmatter = c1.cmatter and c2.cdisbdt = c1.cdisbdt and c2.ccode = c1.ccode and c2.ctk = c1.ctk and c2.cloc = c1.cloc and c2.camount = c1.camount group by 1 order by 1; -- unload duplicates to an ascii file unload to "delete_cost.unl" select c2.* from temp1 c2, cost c1 where c2.cbatch between 59461 and 59466 and c2.cindex <> c1.cindex and c2.cbatch <> c1.cbatch and c2.cmatter = c1.cmatter and c2.cdisbdt = c1.cdisbdt and c2.ccode = c1.ccode and c2.ctk = c1.ctk and c2.cloc = c1.cloc and c2.camount = c1.camount; -- prepare to delete duplicate costs ?? select c2.cindex from temp1 c2, cost c1 where c2.cbatch between 59461 and 59466 and c2.cindex <> c1.cindex and c2.cbatch <> c1.cbatch and c2.cmatter = c1.cmatter and c2.cdisbdt = c1.cdisbdt and c2.ccode = c1.ccode and c2.ctk = c1.ctk and c2.cloc = c1.cloc and c2.camount = c1.camount into temp temp2; -- delete duplicate costs -- delete from cost -- where cindex in (select * from temp2); ################################################################################