return to PRS Technologies website
--##############################################################################
--
-- 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);
################################################################################