/*	===============================================================================================
	Project: ICST | BI2I | Tax Calculator
	-----------------------------------------------------------------------------------------------
	Date: Aug 11, 2017
	Details: 
		1) 	Compute ideal tax to be paid in any year 
	============================================================================================= */

	
/*	Step 01: Compute ideal_tax to be paid in 2008 & 2009
	===============================================================================================
*/
create table bi2i_tax_calc.int_004_ideal_tax as 
select 
*,
(case when first_year <= 2008 then tax_paid_08 else 0 end) as ideal_tax_08,
(case when first_year <= 2008 and tax_paid_09 < tax_paid_08 then tax_paid_08 
	  when tax_paid_09 is null then tax_paid_08 else tax_paid_09 end) as ideal_tax_09
from bi2i_tax_calc.int_003_add_columns_cov;





/*	Step 02: Compute ideal_tax to be paid in 2010
	===============================================================================================
*/
alter table bi2i_tax_calc.int_004_ideal_tax 
add ideal_tax_10 double,
add ideal_tax_11 double,
add ideal_tax_12 double,
add ideal_tax_13 double,
add ideal_tax_14 double,
add ideal_tax_15 double,
add ideal_tax_16 double,
add ideal_tax_17 double;

update bi2i_tax_calc.int_004_ideal_tax
set ideal_tax_10 = (case when first_year <= 2009 and tax_paid_10 < ideal_tax_09 then ideal_tax_09 
						 when tax_paid_10 is null then ideal_tax_09 else tax_paid_10 end);

						 



/*	Step 03: Compute ideal_tax to be paid in 2011
	===============================================================================================
*/
update bi2i_tax_calc.int_004_ideal_tax
set ideal_tax_11 = (case when first_year <= 2010 and tax_paid_11 < ideal_tax_10 then ideal_tax_10 
						 when tax_paid_11 is null then ideal_tax_10 else tax_paid_11 end);

						 
						 


/*	Step 04: Compute ideal_tax to be paid in 2012
	===============================================================================================
*/
update bi2i_tax_calc.int_004_ideal_tax
set ideal_tax_12 = (case when first_year <= 2011 and tax_paid_12 < ideal_tax_11 then ideal_tax_11 
						 when tax_paid_12 is null then ideal_tax_11 else tax_paid_12 end);


						 
						 

/*	Step 05: Compute ideal_tax to be paid in 2013
	===============================================================================================
*/
update bi2i_tax_calc.int_004_ideal_tax
set ideal_tax_13 = (case when first_year <= 2012 and tax_paid_13 < ideal_tax_12 then ideal_tax_12 
						 when tax_paid_13 is null then ideal_tax_12 else tax_paid_13 end);



						 
						 
/*	Step 06: Compute ideal_tax to be paid in 2014
	===============================================================================================
*/
update bi2i_tax_calc.int_004_ideal_tax
set ideal_tax_14 = (case when first_year <= 2013 and tax_paid_14 < ideal_tax_13 then ideal_tax_13 
						 when tax_paid_14 is null then ideal_tax_13 else tax_paid_14 end);


						 
						 

/*	Step 07: Compute ideal_tax to be paid in 2015
	===============================================================================================
*/
update bi2i_tax_calc.int_004_ideal_tax
set ideal_tax_15 = (case when first_year <= 2014 and tax_paid_15 < ideal_tax_14 then ideal_tax_14 
						 when tax_paid_15 is null then ideal_tax_14 else tax_paid_15 end);
						 
	
					 

					 
/*	Step 08: Compare the ideal tax of each year w.r.t coefficient of variance and apply the ideal tax cap
	-----------------------------------------------------------------------------------------------
	Change in logic as captured in the mail sent on Aug 30, 2017
	===============================================================================================
*/
update bi2i_tax_calc.int_004_ideal_tax set 
ideal_tax_09 = (case when ((cov_tax > 0.0) and (ideal_tax_08 < ideal_tax_cap) and (ideal_tax_09 > ideal_tax_cap)) 
				then ideal_tax_cap else ideal_tax_09 end),
ideal_tax_10 = (case when ((cov_tax > 0.0) and (ideal_tax_08 < ideal_tax_cap) and (ideal_tax_10 > ideal_tax_cap)) 
				then ideal_tax_cap else ideal_tax_10 end),
ideal_tax_11 = (case when ((cov_tax > 0.0) and (ideal_tax_08 < ideal_tax_cap) and (ideal_tax_11 > ideal_tax_cap)) 
				then ideal_tax_cap else ideal_tax_11 end),
ideal_tax_12 = (case when ((cov_tax > 0.0) and (ideal_tax_08 < ideal_tax_cap) and (ideal_tax_12 > ideal_tax_cap)) 
				then ideal_tax_cap else ideal_tax_12 end),
ideal_tax_13 = (case when ((cov_tax > 0.0) and (ideal_tax_08 < ideal_tax_cap) and (ideal_tax_13 > ideal_tax_cap)) 
				then ideal_tax_cap else ideal_tax_13 end),
ideal_tax_14 = (case when ((cov_tax > 0.0) and (ideal_tax_08 < ideal_tax_cap) and (ideal_tax_14 > ideal_tax_cap)) 
				then ideal_tax_cap else ideal_tax_14 end),
ideal_tax_15 = (case when ((cov_tax > 0.0) and (ideal_tax_08 < ideal_tax_cap) and (ideal_tax_15 > ideal_tax_cap)) 
				then ideal_tax_cap else ideal_tax_15 end);


						
						

/*	Step 09: Compute ideal_tax to be paid in 2016
	===============================================================================================
*/
update bi2i_tax_calc.int_004_ideal_tax
set ideal_tax_16 = (case when (first_year <= 2015) and (tax_paid_16 < ideal_tax_15) then ideal_tax_15 * 1.2     
							when tax_paid_16 is null then ideal_tax_15 * 1.2 
							else tax_paid_16 end);

						 
						 


/*	Step 10: Compute ideal_tax to be paid in 2017
	===============================================================================================
*/
update bi2i_tax_calc.int_004_ideal_tax
set ideal_tax_17 = (case when (first_year <= 2016) and (tax_paid_17 < ideal_tax_16) then ideal_tax_16 
							when tax_paid_17 is null then ideal_tax_16 else tax_paid_17 end);