/*	===============================================================================================
	Project: ICST | BI2I | Tax Calculator
	-----------------------------------------------------------------------------------------------
	Date: Aug 11, 2017
	Details: 
		1) 	Compute short & defult potential 
	============================================================================================= */

	
/*	Step 01: Compute "last_year" as the year in which tax was last paid
	===============================================================================================
*/
create table bi2i_tax_calc.temp_01 as 
select 
districtid,
sas_base_app_no,
max(low_year) as last_year 
from int_001_mapping_tax_info
group by districtid, sas_base_app_no;


alter table bi2i_tax_calc.int_004_ideal_tax add index (sas_base_app_no);
alter table bi2i_tax_calc.temp_01 add index (sas_base_app_no);


create table bi2i_tax_calc.int_005_potential_calucation as 
select
a.districtid,
a.sas_base_app_no,
a.data_score,
a.first_year,
a.cov_tax,
a.ideal_tax_cap as ideal_tax_cap_till_2015, 
b.last_year,
(case when a.tax_paid_08 is null then 0 else a.tax_paid_08 end) as tax_paid_08,
(case when a.tax_paid_09 is null then 0 else a.tax_paid_09 end) as tax_paid_09,
(case when a.tax_paid_10 is null then 0 else a.tax_paid_10 end) as tax_paid_10,
(case when a.tax_paid_11 is null then 0 else a.tax_paid_11 end) as tax_paid_11,
(case when a.tax_paid_12 is null then 0 else a.tax_paid_12 end) as tax_paid_12,
(case when a.tax_paid_13 is null then 0 else a.tax_paid_13 end) as tax_paid_13,
(case when a.tax_paid_14 is null then 0 else a.tax_paid_14 end) as tax_paid_14,
(case when a.tax_paid_15 is null then 0 else a.tax_paid_15 end) as tax_paid_15,
(case when a.tax_paid_16 is null then 0 else a.tax_paid_16 end) as tax_paid_16,
(case when a.tax_paid_17 is null then 0 else a.tax_paid_17 end) as tax_paid_17,
(case when a.ideal_tax_08 is null then 0 else a.ideal_tax_08 end) as ideal_tax_08,
(case when a.ideal_tax_09 is null then 0 else a.ideal_tax_09 end) as ideal_tax_09,
(case when a.ideal_tax_10 is null then 0 else a.ideal_tax_10 end) as ideal_tax_10,
(case when a.ideal_tax_11 is null then 0 else a.ideal_tax_11 end) as ideal_tax_11,
(case when a.ideal_tax_12 is null then 0 else a.ideal_tax_12 end) as ideal_tax_12,
(case when a.ideal_tax_13 is null then 0 else a.ideal_tax_13 end) as ideal_tax_13,
(case when a.ideal_tax_14 is null then 0 else a.ideal_tax_14 end) as ideal_tax_14,
(case when a.ideal_tax_15 is null then 0 else a.ideal_tax_15 end) as ideal_tax_15,
(case when a.ideal_tax_16 is null then 0 else a.ideal_tax_16 end) as ideal_tax_16,
(case when a.ideal_tax_17 is null then 0 else a.ideal_tax_17 end) as ideal_tax_17
from bi2i_tax_calc.int_004_ideal_tax as a 
left join temp_01 as b 
on ((a.districtid = b.districtid) and (a.sas_base_app_no = b.sas_base_app_no));

drop table bi2i_tax_calc.temp_01;