Class 3.1 – Cleaning

create table cases as
select
casetype::text,
casenumber::text,
adsakey::int,
assignedinspcode::int,
assignment::text,
entrydate::date,
lastupdate::date,
duedate::date,
closedate::date,
closereason::text,
casenotes::text,
origin::text,
casestatus::text,
rentalstatus::text,
censustract::text,
-- need to reassert column name. move from UPPERCASE to Normal
initcap(lower(ownername)) as ownername,
-- shift over the ownermailaddr. use case statement as a value
initcap(lower(case when ownermailaddr is null then null else ownername2 end)) as ownername2,
initcap(lower(case when ownermailaddr is null then ownername2 else ownermailaddr end)) as ownermail,
ownermailaddr2,
initcap(lower(ownermailcity)) as ownermailcity,
ownermailstate,
-- just use 5 char zipz
substring(ownermailzip from 1 for 5) as ownermailzip,
usecode::text,
adlot::text,
zoning::text,
initcap(lower(annexdate)) as annexdate,
stnumber::int,
stapt::text,
stpfxdir::text,
initcap(lower(stname))::text as stname,
sttype::text,
stsfxdir::text,
city::text,
state::text,
xcoord::real,
ycoord::real,
initcap(lower(fulladdress))::text
from loe_all_cases;

create table inspections as
select
inspectionid::text,
inspectionsakey::int,
casesakey::int,
adsakey::int,
casetype::text,
casenumber::text,
inspectiondate::date,
inspectiontype::text,
inspector::text,
-- select distinct compliant from inspections;
case when compliant = 'Y' then true when compliant = 'N' then false else null end as compliant,
-- select distinct unfounded from inspections;
case when unfounded is null then false else true end as unfounded,
inspectiontypedesc::text,
inspectionnotes::text,
entrydate::date,
lastupdate::date,
casestatus::text
-- no street address needed
from loe_all_inspections

create table violations as
select
violationid::text,
violationsakey::int,
casesakey::int,
adsakey::int,
casetype::text,
casenumber::text,
numberofitems::int,
violationcode::text,
responsibleparty::text,
violationdescription::text,
comments::text,
majorviolation::text,
case when violationissued = 'Y' then true when violationissued = 'N' then false else null end as violationissued,
issueddate::date,
issuedby::text,
case when violationcleared = 'Y' then true when violationcleared = 'N' then false else null end as violationcleared,
cleardate::date,
clearedby::text,
case when violationreissued = 'Y' then true when violationreissued = 'N' then false else null end as violationreissued,
reissuedate::date,
reissuedby::text,
entrydate::date,
lastupdate::date,
casestatus::text
from loe_all_violations;

create table case_history as
select
casehistoryid::text,
casehistorysakey::int,
casesakey::int,
adsakey::int,
casetype::text,
casenumber::text,
-- reserved word
date::date as entrydate,
actioncode::text,
action::text,
inspector::text,
casestatus::text
from loe_case_history;