fec_update.sql

# FEC Updating Process SQL

# updating recind

SELECT * INTO [DevFEC].[fec_user].[lk_recindbak] FROM [DevFEC].[fec_user].[lk_recind] WHERE CYCLE='04';

DELETE FROM [DevFEC].[fec_user].[lk_recind] WHERE CYCLE='04';

INSERT INTO [DevFEC].[fec_user].[lk_recind]([filer_id], [trans_type], [INCLUDE], [CONTRIB], [PREFIX], [NICKNAME], [LNAME], [MI], [FNAME], [SUFFIX], [city], [state], [zip], [occupation], [employer], [newocc], [orgid], [personid], [recno], [datenew], [amountnew], [otherid], [cycle], [datafile], [timestamp], [notes])

SELECT [FILER_ID],[TRANS_TYPE], null as include, [CONTRIB], [PREFIX], [NICKNAME], [LNAME], [MI], [FNAME], [SUFFIX], [CITY], [STATE], [ZIP], [OCCUPATION], null as employer, null as newocc, null as orgid, null as personid, [RECNO], [DATENEW], [AMOUNTNEW], [OTHER_ID], '04' as [CYCLE], 'dta_individ04' as [DATAFILE], current_timestamp as timestamp, null as notes

FROM [DevFEC].[fec_user].[temp_INDIVID] 
WHERE trans_type='10' or trans_type like '15' or trans_type='15C' or trans_type='15E' or trans_type='15I' or trans_type='15J' or trans_type='22Y';

# insert 15Js into recind 
INSERT INTO [DevFEC].[fec_user].[lk_recind]([filer_id], [trans_type], [INCLUDE], [contrib], [PREFIX], [NICKNAME], [LNAME], [MI], [FNAME], [SUFFIX], [city], [state], [zip], [occupation], [employer], [newocc], [orgid], [personid], [recno], [datenew], [amountnew], [otherid], [cycle], [datafile], [timestamp], [notes])

select [FILER_ID],[TRANS_TYPE], null as include, contrib, PREFIX, NICKNAME, LNAME, MI, [FNAME], SUFFIX, [CITY], [STATE], [ZIP], [OCCUPATION], null as employer, null as newocc, null as orgid, null as personid, [RECNO], [DATENEW], [AMOUNTNEW], [OTHER_ID], '04' as CYCLE, 'dta_cmtee04' as [DATAFILE], current_timestamp as timestamp, null as notes

FROM [DevFEC].[fec_user].[temp_CMTEE] 
where trans_type='15J';

# populating employer and newocc

update [DevFEC].[fec_user].[lk_recind] 
set employer = left(occupation, charindex('/', occupation)) 
where cycle='04' and charindex('/', occupation)>0;

update [DevFEC].[fec_user].[lk_recind] 
set newocc = substring(occupation, charindex('/', occupation), 25) 
where occupation like '%/%' and cycle='04';

# removing / from employer & newocc fields

update [DevFEC].[fec_user].[lk_recind] 
set employer=substring(employer,1,charindex('/',employer)-1) 
where employer like '%/%' and cycle='04';

update [DevFEC].[fec_user].[lk_recind] 
set newocc=substring(newocc,2,35) 
where newocc like '%/%' and cycle='04';

update [DevFEC].[fec_user].[lk_recind] 
set employer = occupation 
where employer is null;

# removing common occupations from employer (could do this separately) 
update [DevFEC].[fec_user].[lk_recind] 
set employer=NULL 
where employer='STUDENT' OR employer='LOBBYIST' or employer='BROKER' or employer='CAR DEALER' or employer='SALESMAN' or employer='DEVELOPER' or employer='OILMAN' or employer='GEOLOGIST' OR employer='CONSTRUCTION' or employer='LOGGING' or employer='CONTRACTOR' or employer='LOGGER' or employer='PSYCHOLOGIST' or employer='RESTAURANTEUR' OR employer='HOUSEWIFE' OR employer='HOMEMAKER' OR employer='RETIRED' OR employer='DOCTOR' OR employer='LAWYER' OR employer='ATTORNEY' OR employer='PHYSICIAN' OR employer='TEACHER';

# do orgmatch 
update [DevFEC].[fec_user].[lk_recind] 
set [DevFEC].[fec_user].[lk_recind].orgid=[DevFEC].[fec_user].[lk_orgmatch].orgid 
from [DevFEC].[fec_user].[lk_orgmatch] 
where [DevFEC].[fec_user].[lk_recind].employer=[DevFEC].[fec_user].[lk_orgmatch].name and [DevFEC].[fec_user].[lk_recind].cycle='04' and [DevFEC].[fec_user].[lk_recind].orgid is null;