log_user 'ORPTS','IN'; commit;

DROP TABLE IF EXISTS star_tax_credit_data; commit;

create table star_tax_credit_data (
        swis                char(6) not null,
        parcel_id           integer not null,
        print_key           char(25) not null,
        county_name         varchar(99) not null,
        muni_name           varchar(99) not null,
        mailing_line_1      varchar(99) not null,
        mailing_line_2      varchar(99) not null,
        mailing_line_3      varchar(99) not null,
        section             char(3) not null,
        sub_section         char(3) not null,
        block               char(4) not null,
        lot                 char(3) not null,
        sub_lot             char(3) not null,
        suffix              char(4) not null,
        ex_code             char(5) not null,
        sch_code            char(6) not null,
        sch_name            varchar(99) not null,
        inclusion_msg       varchar(200) not null,
primary key (swis, parcel_id, ex_code)); commit;

// Capture all STAR Exemptions with an INITIAL YEAR of 2016
insert into star_tax_credit_data
select distinct p.swis, p.parcel_id, print_key, '','','','','', p.section, p.sub_section, p.block, p.lot, p.sub_lot, p.suffix, e1.ex_code, a1.sch_code,'',
                'STAR Exemption with an INITIAL YEAR of 2016'
  from parcel p, assessment a1, exempt e1
 where p.swis = a1.swis and p.parcel_id = a1.parcel_id and a1.roll_yr = 2016
   and p.swis = e1.swis and p.parcel_id = e1.parcel_id and e1.roll_yr = 2016
   and e1.ex_code in ('41834','41844','41836','41846','41854','41864','41856','41866')
   and e1.ex_init_yr >= 2016
   and not a1.prop_class = '416'
   and not a1.own_code in ('P','Q')
; commit;

// Remove all parcels having a BASIC STAR in 2016 and having had a BASIC STAR in 2015, 2014 or 2013 AND common ownership
delete from star_tax_credit_data
  from exempt e1, exempt e2, assessment a1, assessment a2
 where e1.swis = e2.swis and e1.parcel_id = e2.parcel_id and e1.roll_yr = 2016 and e2.roll_yr = 2015
   and e1.ex_code in ('41854','41864','41856','41866')
   and e2.ex_code in ('41854','41864','41856','41866')
   and star_tax_credit_data.swis = e1.swis and star_tax_credit_data.parcel_id = e1.parcel_id
   and star_tax_credit_data.swis = e2.swis and star_tax_credit_data.parcel_id = e2.parcel_id
   and star_tax_credit_data.swis = a1.swis and star_tax_credit_data.parcel_id = a1.parcel_id
   and star_tax_credit_data.swis = a2.swis and star_tax_credit_data.parcel_id = a2.parcel_id
   and a1.roll_yr = 2016 and a2.roll_yr = 2015 and a1.primary_owner = a2.primary_owner
; commit;

delete from star_tax_credit_data
  from exempt e1, exempt e2, assessment a1, assessment a2
 where e1.swis = e2.swis and e1.parcel_id = e2.parcel_id and e1.roll_yr = 2016 and e2.roll_yr = 2014
   and e1.ex_code in ('41854','41864','41856','41866')
   and e2.ex_code in ('41854','41864','41856','41866')
   and star_tax_credit_data.swis = e1.swis and star_tax_credit_data.parcel_id = e1.parcel_id
   and star_tax_credit_data.swis = e2.swis and star_tax_credit_data.parcel_id = e2.parcel_id
   and star_tax_credit_data.swis = a1.swis and star_tax_credit_data.parcel_id = a1.parcel_id
   and star_tax_credit_data.swis = a2.swis and star_tax_credit_data.parcel_id = a2.parcel_id
   and a1.roll_yr = 2016 and a2.roll_yr = 2014 and a1.primary_owner = a2.primary_owner
; commit;

delete from star_tax_credit_data
  from exempt e1, exempt e2, assessment a1, assessment a2
 where e1.swis = e2.swis and e1.parcel_id = e2.parcel_id and e1.roll_yr = 2016 and e2.roll_yr = 2013
   and e1.ex_code in ('41854','41864','41856','41866')
   and e2.ex_code in ('41854','41864','41856','41866')
   and star_tax_credit_data.swis = e1.swis and star_tax_credit_data.parcel_id = e1.parcel_id
   and star_tax_credit_data.swis = e2.swis and star_tax_credit_data.parcel_id = e2.parcel_id
   and star_tax_credit_data.swis = a1.swis and star_tax_credit_data.parcel_id = a1.parcel_id
   and star_tax_credit_data.swis = a2.swis and star_tax_credit_data.parcel_id = a2.parcel_id
   and a1.roll_yr = 2016 and a2.roll_yr = 2013 and a1.primary_owner = a2.primary_owner
; commit;

// Remove all parcels having a BASIC STAR in 2016 and having had an ENHANCED STAR in 2015, 2014 or 2013 AND common ownership
delete from star_tax_credit_data
  from exempt e1, exempt e2, assessment a1, assessment a2
 where e1.swis = e2.swis and e1.parcel_id = e2.parcel_id and e1.roll_yr = 2016 and e2.roll_yr = 2015
   and e1.ex_code in ('41854','41864','41856','41866')
   and e2.ex_code in ('41834','41844','41836','41846')
   and star_tax_credit_data.swis = e1.swis and star_tax_credit_data.parcel_id = e1.parcel_id
   and star_tax_credit_data.swis = e2.swis and star_tax_credit_data.parcel_id = e2.parcel_id
   and star_tax_credit_data.swis = a1.swis and star_tax_credit_data.parcel_id = a1.parcel_id
   and star_tax_credit_data.swis = a2.swis and star_tax_credit_data.parcel_id = a2.parcel_id
   and a1.roll_yr = 2016 and a2.roll_yr = 2015 and a1.primary_owner = a2.primary_owner
; commit;

delete from star_tax_credit_data
  from exempt e1, exempt e2, assessment a1, assessment a2
 where e1.swis = e2.swis and e1.parcel_id = e2.parcel_id and e1.roll_yr = 2016 and e2.roll_yr = 2014
   and e1.ex_code in ('41854','41864','41856','41866')
   and e2.ex_code in ('41834','41844','41836','41846')
   and star_tax_credit_data.swis = e1.swis and star_tax_credit_data.parcel_id = e1.parcel_id
   and star_tax_credit_data.swis = e2.swis and star_tax_credit_data.parcel_id = e2.parcel_id
   and star_tax_credit_data.swis = a1.swis and star_tax_credit_data.parcel_id = a1.parcel_id
   and star_tax_credit_data.swis = a2.swis and star_tax_credit_data.parcel_id = a2.parcel_id
   and a1.roll_yr = 2016 and a2.roll_yr = 2014 and a1.primary_owner = a2.primary_owner
; commit;

delete from star_tax_credit_data
  from exempt e1, exempt e2, assessment a1, assessment a2
 where e1.swis = e2.swis and e1.parcel_id = e2.parcel_id and e1.roll_yr = 2016 and e2.roll_yr = 2013
   and e1.ex_code in ('41854','41864','41856','41866')
   and e2.ex_code in ('41834','41844','41836','41846')
   and star_tax_credit_data.swis = e1.swis and star_tax_credit_data.parcel_id = e1.parcel_id
   and star_tax_credit_data.swis = e2.swis and star_tax_credit_data.parcel_id = e2.parcel_id
   and star_tax_credit_data.swis = a1.swis and star_tax_credit_data.parcel_id = a1.parcel_id
   and star_tax_credit_data.swis = a2.swis and star_tax_credit_data.parcel_id = a2.parcel_id
   and a1.roll_yr = 2016 and a2.roll_yr = 2013 and a1.primary_owner = a2.primary_owner
; commit;

// Remove all parcels having an ENHANCED STAR in 2016 and having had a BASIC STAR in 2015, 2014 or 2013 AND common ownership
delete from star_tax_credit_data
  from exempt e1, exempt e2, assessment a1, assessment a2
 where e1.swis = e2.swis and e1.parcel_id = e2.parcel_id and e1.roll_yr = 2016 and e2.roll_yr = 2015
   and e1.ex_code in ('41834','41844','41836','41846')
   and e2.ex_code in ('41854','41864','41856','41866')
   and star_tax_credit_data.swis = e1.swis and star_tax_credit_data.parcel_id = e1.parcel_id
   and star_tax_credit_data.swis = e2.swis and star_tax_credit_data.parcel_id = e2.parcel_id
   and star_tax_credit_data.swis = a1.swis and star_tax_credit_data.parcel_id = a1.parcel_id
   and star_tax_credit_data.swis = a2.swis and star_tax_credit_data.parcel_id = a2.parcel_id
   and a1.roll_yr = 2016 and a2.roll_yr = 2015 and a1.primary_owner = a2.primary_owner
; commit;

delete from star_tax_credit_data
  from exempt e1, exempt e2, assessment a1, assessment a2
 where e1.swis = e2.swis and e1.parcel_id = e2.parcel_id and e1.roll_yr = 2016 and e2.roll_yr = 2014
   and e1.ex_code in ('41834','41844','41836','41846')
   and e2.ex_code in ('41854','41864','41856','41866')
   and star_tax_credit_data.swis = e1.swis and star_tax_credit_data.parcel_id = e1.parcel_id
   and star_tax_credit_data.swis = e2.swis and star_tax_credit_data.parcel_id = e2.parcel_id
   and star_tax_credit_data.swis = a1.swis and star_tax_credit_data.parcel_id = a1.parcel_id
   and star_tax_credit_data.swis = a2.swis and star_tax_credit_data.parcel_id = a2.parcel_id
   and a1.roll_yr = 2016 and a2.roll_yr = 2014 and a1.primary_owner = a2.primary_owner
; commit;

delete from star_tax_credit_data
  from exempt e1, exempt e2, assessment a1, assessment a2
 where e1.swis = e2.swis and e1.parcel_id = e2.parcel_id and e1.roll_yr = 2016 and e2.roll_yr = 2013
   and e1.ex_code in ('41834','41844','41836','41846')
   and e2.ex_code in ('41854','41864','41856','41866')
   and star_tax_credit_data.swis = e1.swis and star_tax_credit_data.parcel_id = e1.parcel_id
   and star_tax_credit_data.swis = e2.swis and star_tax_credit_data.parcel_id = e2.parcel_id
   and star_tax_credit_data.swis = a1.swis and star_tax_credit_data.parcel_id = a1.parcel_id
   and star_tax_credit_data.swis = a2.swis and star_tax_credit_data.parcel_id = a2.parcel_id
   and a1.roll_yr = 2016 and a2.roll_yr = 2013 and a1.primary_owner = a2.primary_owner
; commit;

// Remove all parcels having an ENHANCED STAR in 2016 and having had an ENHANCED STAR in 2015, 2014 or 2013 AND common ownership
delete from star_tax_credit_data
  from exempt e1, exempt e2, assessment a1, assessment a2
 where e1.swis = e2.swis and e1.parcel_id = e2.parcel_id and e1.roll_yr = 2016 and e2.roll_yr = 2015
   and e1.ex_code in ('41834','41844','41836','41846')
   and e2.ex_code in ('41834','41844','41836','41846')
   and star_tax_credit_data.swis = e1.swis and star_tax_credit_data.parcel_id = e1.parcel_id
   and star_tax_credit_data.swis = e2.swis and star_tax_credit_data.parcel_id = e2.parcel_id
   and star_tax_credit_data.swis = a1.swis and star_tax_credit_data.parcel_id = a1.parcel_id
   and star_tax_credit_data.swis = a2.swis and star_tax_credit_data.parcel_id = a2.parcel_id
   and a1.roll_yr = 2016 and a2.roll_yr = 2015 and a1.primary_owner = a2.primary_owner
; commit;

delete from star_tax_credit_data
  from exempt e1, exempt e2, assessment a1, assessment a2
 where e1.swis = e2.swis and e1.parcel_id = e2.parcel_id and e1.roll_yr = 2016 and e2.roll_yr = 2014
   and e1.ex_code in ('41834','41844','41836','41846')
   and e2.ex_code in ('41834','41844','41836','41846')
   and star_tax_credit_data.swis = e1.swis and star_tax_credit_data.parcel_id = e1.parcel_id
   and star_tax_credit_data.swis = e2.swis and star_tax_credit_data.parcel_id = e2.parcel_id
   and star_tax_credit_data.swis = a1.swis and star_tax_credit_data.parcel_id = a1.parcel_id
   and star_tax_credit_data.swis = a2.swis and star_tax_credit_data.parcel_id = a2.parcel_id
   and a1.roll_yr = 2016 and a2.roll_yr = 2014 and a1.primary_owner = a2.primary_owner
; commit;

delete from star_tax_credit_data
  from exempt e1, exempt e2, assessment a1, assessment a2
 where e1.swis = e2.swis and e1.parcel_id = e2.parcel_id and e1.roll_yr = 2016 and e2.roll_yr = 2013
   and e1.ex_code in ('41834','41844','41836','41846')
   and e2.ex_code in ('41834','41844','41836','41846')
   and star_tax_credit_data.swis = e1.swis and star_tax_credit_data.parcel_id = e1.parcel_id
   and star_tax_credit_data.swis = e2.swis and star_tax_credit_data.parcel_id = e2.parcel_id
   and star_tax_credit_data.swis = a1.swis and star_tax_credit_data.parcel_id = a1.parcel_id
   and star_tax_credit_data.swis = a2.swis and star_tax_credit_data.parcel_id = a2.parcel_id
   and a1.roll_yr = 2016 and a2.roll_yr = 2013 and a1.primary_owner = a2.primary_owner
; commit;

// Remove all parcels having a NEW PRIMARY OWNER in 2016 who was an ADDITIONAL OWNER in 2015
delete from star_tax_credit_data
  from assessment a1, parcel_to_owner p, owner o
 where star_tax_credit_data.swis = a1.swis and star_tax_credit_data.parcel_id = a1.parcel_id
   and a1.roll_yr = 2016
   and star_tax_credit_data.swis = p.swis and star_tax_credit_data.parcel_id = p.parcel_id
   and p.roll_yr = 2015
   and a1.primary_owner = RTRIM(o.owner_last_name || ', ' || o.owner_first_name || ' ' || o.owner_initial_name || ' ' || o.owner_name_suffix)
   and p.swis = o.swis and p.parcel_id = o.parcel_id and p.owner_id = o.owner_id and o.owner_type = 'A'
; commit;

// Populate COUNTY NAME
update star_tax_credit_data
   set star_tax_credit_data.county_name = 'County of ' + county.county_name
  from county
 where left(star_tax_credit_data.swis,2) = county.swis_co
   and county.roll_yr = 2016
; commit;

// Populate MUNICIPAL NAME
update star_tax_credit_data
   set star_tax_credit_data.muni_name = 'City of ' + swis.muni_nm
  from swis
 where star_tax_credit_data.swis = swis.swis
   and substr(star_tax_credit_data.swis,3,2) < '20'
   and swis.roll_yr = 2016
; commit;

update star_tax_credit_data
   set star_tax_credit_data.muni_name = 'Town of ' + swis.muni_nm
  from swis
 where star_tax_credit_data.swis = swis.swis
   and substr(star_tax_credit_data.swis,3,2) >= '20'
   and swis.roll_yr = 2016
; commit;

// Populate SCHOOL DISTRICT NAME
update star_tax_credit_data
   set star_tax_credit_data.sch_name = school_ref.school_name
  from school_ref
 where star_tax_credit_data.sch_code = school_ref.sch_code
   and school_ref.roll_yr = 2016
; commit;

// Populate OWNER NAME and MAILING ADDRESS
update star_tax_credit_data
   set mailing_line_1 = Trim(IFNULL(owner.owner_first_name,'', TRIM(owner.owner_first_name) + ' ' ) + If Trim(owner.owner_initial_name) = '' Then '' Else 
                                Trim(owner.owner_initial_name) + '. ' EndIf  +  IFNull(owner.owner_last_name, '', owner.owner_last_name) + ' ' + Trim(owner.owner_name_suffix)  ),
       mailing_line_2 = (if trim (owner.mail_st_nbr) = '' then '' else trim(owner.mail_st_nbr ) endif+
                if trim (owner.prefix_dir) = '' then '' else ' ' + trim(owner.prefix_dir) endif+          
                if trim (owner.mail_st_rt) = '' then '' else ' ' + trim(owner.mail_st_rt) endif+
                if trim( owner.own_mail_st_suff) = '' then '' else ' ' + trim(owner.own_mail_st_suff) endif+
                if trim( owner.post_dir ) = '' then '' else ' ' + trim ( owner.post_dir ) endif),
       mailing_line_3 = (if trim (owner.mail_city) = '' then '' else trim (owner.mail_city) endif +
                if trim(owner.own_mail_state) = '' then '' else ', ' + trim(owner.own_mail_state) endif +  
                if length (trim(owner.mail_zip)) < 6 then '  ' + trim(owner.mail_zip) else
                '  ' + left(trim(owner.mail_zip),5) + '-' + substr(trim(owner.mail_zip),6) endif)
  from owner, parcel_to_owner
 where owner.swis = parcel_to_owner.swis and owner.parcel_id = parcel_to_owner.parcel_id and owner.owner_id = parcel_to_owner.owner_id
   and parcel_to_owner.roll_yr = 2016
   and owner.owner_type = 'P'
   and star_tax_credit_data.swis = owner.swis and star_tax_credit_data.parcel_id = owner.parcel_id
   and star_tax_credit_data.swis = parcel_to_owner.swis and star_tax_credit_data.parcel_id = parcel_to_owner.parcel_id
; commit;

update star_tax_credit_data
   set mailing_line_2 = 'PO Box ' + owner.po_box
  from owner, parcel_to_owner
 where owner.swis = parcel_to_owner.swis and owner.parcel_id = parcel_to_owner.parcel_id and owner.owner_id = parcel_to_owner.owner_id
   and parcel_to_owner.roll_yr = 2016
   and owner.owner_type = 'P' and trim(owner.po_box) > ''
   and star_tax_credit_data.swis = owner.swis and star_tax_credit_data.parcel_id = owner.parcel_id
   and star_tax_credit_data.swis = parcel_to_owner.swis and star_tax_credit_data.parcel_id = parcel_to_owner.parcel_id
; commit;

// Provide ADDITIONAL INFORMATION for parcels having been identified as NEW OWNERSHIP
update star_tax_credit_data
   set inclusion_msg = inclusion_msg + ' and a change in ownership'
  from assessment a1, assessment a2
 where star_tax_credit_data.swis = a1.swis and star_tax_credit_data.parcel_id = a1.parcel_id
   and star_tax_credit_data.swis = a2.swis and star_tax_credit_data.parcel_id = a2.parcel_id
   and a1.roll_yr = 2016 and a2.roll_yr = 2015 and NOT a1.primary_owner = a2.primary_owner
; commit;

// Provide ADDITIONAL INFORMATION for parcels having been identified as SPLITS/MERGES/NEW PARCELS
update star_tax_credit_data
   set inclusion_msg = inclusion_msg + ' and was likely subject to split, merge or is a new parcel'
  from assessment a1
 where star_tax_credit_data.swis = a1.swis and star_tax_credit_data.parcel_id = a1.parcel_id
   and a1.roll_yr = 2016 and NOT a1.parcel_id in (select a2.parcel_id from assessment a2 where a1.swis = a2.swis and a2.roll_yr = 2015)
; commit;

// Provide ADDITIONAL INFORMATION for parcels having been identified as POTENTIAL TRUST SALES
update star_tax_credit_data
   set inclusion_msg = inclusion_msg + ' and is possibly a Trust Sale.'
  from sale s
 where star_tax_credit_data.swis = s.swis and star_tax_credit_data.parcel_id = s.parcel_id
   and date(s.sale_date) > date('2015-03-02')
   and s.book+s.page in (select sc.book+sc.page from salecond_to_sale sc where s.book = sc.book and s.page = sc.page and salecond_code IN ('A','C'))
; commit;

// Generate final result set
select inclusion_msg, * from star_tax_credit_data
 order by swis, section, sub_section, block, lot, sub_lot, suffix