-- REAL AND PERSONAL 

select 'REAL+PERSONAL' as PROPTYPE, sum(SCHOOL_ESTTAX) as SCHOOL_ESTTAX from (
select bill_year, sum(school_esttax) as SCHOOL_ESTTAX from (
select b.BILL_NO, b.BILL_YEAR
	, vb.ADV_TAX
	, vb.ADV_TAX_BALANCE
	, td.ESTTAX
	, round(td.ESTTAX * vb.ADV_TAX_BALANCE / vb.ADV_TAX ,2) as SCHOOL_ESTTAX
from dy2022.dbo.vBILL_BALANCE vb 
inner join dy2022.dbo.bill b on b.BILL_NO=vb.BILL_NO and b.BILL_TYPE=vb.BILL_TYPE and b.BILL_YEAR=vb.BILL_YEAR and vb.ADV_TAX_BALANCE>0
inner join dy2022.dbo.property p on p.PROPERTYKEY=b.PROPERTYKEY 
inner join dy2022.dbo.TAXDETAIL td on td.PROPERTYKEY=b.PROPERTYKEY and td.TAXTYPE=3	
where b.bill_type IN ('R','P') 
	and b.BALANCE_DUE > 0
	and isnull(b.UTILITY,0)=0
	and isnull(p.NOD,0)=0
) q group by bill_year

union all 

select bill_year, sum(school_esttax) from (
select b.BILL_NO, b.BILL_YEAR
	, vb.ADV_TAX
	, vb.ADV_TAX_BALANCE
	, td.ESTTAX
	, round(td.ESTTAX * vb.ADV_TAX_BALANCE / vb.ADV_TAX ,2) as SCHOOL_ESTTAX
from dy2021.dbo.vBILL_BALANCE vb 
inner join dy2021.dbo.bill b on b.BILL_NO=vb.BILL_NO and b.BILL_TYPE=vb.BILL_TYPE and b.BILL_YEAR=vb.BILL_YEAR and vb.ADV_TAX_BALANCE>0
inner join dy2021.dbo.property p on p.PROPERTYKEY=b.PROPERTYKEY 
inner join dy2021.dbo.TAXDETAIL td on td.PROPERTYKEY=b.PROPERTYKEY and td.TAXTYPE=3	
where b.bill_type IN ('R','P')
	and b.BALANCE_DUE > 0
	and isnull(b.UTILITY,0)=0
	and isnull(p.NOD,0)=0
) q group by bill_year

union all 

select bill_year, sum(school_esttax) from (
select b.BILL_NO, b.BILL_YEAR
	, vb.ADV_TAX
	, vb.ADV_TAX_BALANCE
	, td.ESTTAX
	, round(td.ESTTAX * vb.ADV_TAX_BALANCE / vb.ADV_TAX ,2) as SCHOOL_ESTTAX
from dy2020.dbo.vBILL_BALANCE vb 
inner join dy2020.dbo.bill b on b.BILL_NO=vb.BILL_NO and b.BILL_TYPE=vb.BILL_TYPE and b.BILL_YEAR=vb.BILL_YEAR and vb.ADV_TAX_BALANCE>0
inner join dy2020.dbo.property p on p.PROPERTYKEY=b.PROPERTYKEY 
inner join dy2020.dbo.TAXDETAIL td on td.PROPERTYKEY=b.PROPERTYKEY and td.TAXTYPE=3	
where b.bill_type IN ('R','P')
	and b.BALANCE_DUE > 0
	and isnull(b.UTILITY,0)=0
	and isnull(p.NOD,0)=0
) q group by bill_year

union all 

select bill_year, sum(school_esttax) from (
select b.BILL_NO, b.BILL_YEAR
	, vb.ADV_TAX
	, vb.ADV_TAX_BALANCE
	, td.ESTTAX
	, round(td.ESTTAX * vb.ADV_TAX_BALANCE / vb.ADV_TAX ,2) as SCHOOL_ESTTAX
from dy2019.dbo.vBILL_BALANCE vb 
inner join dy2019.dbo.bill b on b.BILL_NO=vb.BILL_NO and b.BILL_TYPE=vb.BILL_TYPE and b.BILL_YEAR=vb.BILL_YEAR and vb.ADV_TAX_BALANCE>0
inner join dy2019.dbo.property p on p.PROPERTYKEY=b.PROPERTYKEY 
inner join dy2019.dbo.TAXDETAIL td on td.PROPERTYKEY=b.PROPERTYKEY and td.TAXTYPE=3	
where b.bill_type IN ('R','P')
	and b.BALANCE_DUE > 0
	and isnull(b.UTILITY,0)=0
	and isnull(p.NOD,0)=0
) q group by bill_year

union all 

select bill_year, sum(school_esttax) from (
select b.BILL_NO, b.BILL_YEAR
	, vb.ADV_TAX
	, vb.ADV_TAX_BALANCE
	, td.ESTTAX
	, round(td.ESTTAX * vb.ADV_TAX_BALANCE / vb.ADV_TAX ,2) as SCHOOL_ESTTAX
from dy2018.dbo.vBILL_BALANCE vb 
inner join dy2018.dbo.bill b on b.BILL_NO=vb.BILL_NO and b.BILL_TYPE=vb.BILL_TYPE and b.BILL_YEAR=vb.BILL_YEAR and vb.ADV_TAX_BALANCE>0
inner join dy2018.dbo.property p on p.PROPERTYKEY=b.PROPERTYKEY 
inner join dy2018.dbo.TAXDETAIL td on td.PROPERTYKEY=b.PROPERTYKEY and td.TAXTYPE=3	
where b.bill_type IN ('R','P')
	and b.BALANCE_DUE > 0
	and isnull(b.UTILITY,0)=0
	and isnull(p.NOD,0)=0
) q group by bill_year

union all 

select bill_year, sum(school_esttax) from (
select b.BILL_NO, b.BILL_YEAR
	, vb.ADV_TAX
	, vb.ADV_TAX_BALANCE
	, td.ESTTAX
	, round(td.ESTTAX * vb.ADV_TAX_BALANCE / vb.ADV_TAX ,2) as SCHOOL_ESTTAX
from dy2017.dbo.vBILL_BALANCE vb 
inner join dy2017.dbo.bill b on b.BILL_NO=vb.BILL_NO and b.BILL_TYPE=vb.BILL_TYPE and b.BILL_YEAR=vb.BILL_YEAR and vb.ADV_TAX_BALANCE>0
inner join dy2017.dbo.property p on p.PROPERTYKEY=b.PROPERTYKEY 
inner join dy2017.dbo.TAXDETAIL td on td.PROPERTYKEY=b.PROPERTYKEY and td.TAXTYPE=3	
where b.bill_type IN ('R','P')
	and b.BALANCE_DUE > 0
	and isnull(b.UTILITY,0)=0
	and isnull(p.NOD,0)=0
) q group by bill_year

union all 

select bill_year, sum(school_esttax) from (
select b.BILL_NO, b.BILL_YEAR
	, vb.ADV_TAX
	, vb.ADV_TAX_BALANCE
	, td.ESTTAX
	, round(td.ESTTAX * vb.ADV_TAX_BALANCE / vb.ADV_TAX ,2) as SCHOOL_ESTTAX
from dy2016.dbo.vBILL_BALANCE vb 
inner join dy2016.dbo.bill b on b.BILL_NO=vb.BILL_NO and b.BILL_TYPE=vb.BILL_TYPE and b.BILL_YEAR=vb.BILL_YEAR and vb.ADV_TAX_BALANCE>0
inner join dy2016.dbo.property p on p.PROPERTYKEY=b.PROPERTYKEY 
inner join dy2016.dbo.TAXDETAIL td on td.PROPERTYKEY=b.PROPERTYKEY and td.TAXTYPE=3	
where b.bill_type IN ('R','P')
	and b.BALANCE_DUE > 0
	and isnull(b.UTILITY,0)=0
	and isnull(p.NOD,0)=0
) q group by bill_year

union all 

select bill_year, sum(school_esttax) from (
select b.BILL_NO, b.BILL_YEAR
	, vb.ADV_TAX
	, vb.ADV_TAX_BALANCE
	, td.ESTTAX
	, round(td.ESTTAX * vb.ADV_TAX_BALANCE / vb.ADV_TAX ,2) as SCHOOL_ESTTAX
from dy2015.dbo.vBILL_BALANCE vb 
inner join dy2015.dbo.bill b on b.BILL_NO=vb.BILL_NO and b.BILL_TYPE=vb.BILL_TYPE and b.BILL_YEAR=vb.BILL_YEAR and vb.ADV_TAX_BALANCE>0
inner join dy2015.dbo.property p on p.PROPERTYKEY=b.PROPERTYKEY 
inner join dy2015.dbo.TAXDETAIL td on td.PROPERTYKEY=b.PROPERTYKEY and td.TAXTYPE=3	
where b.bill_type IN ('R','P')
	and b.BALANCE_DUE > 0
	and isnull(b.UTILITY,0)=0
	and isnull(p.NOD,0)=0
) q group by bill_year

union all 

select bill_year, sum(school_esttax) from (
select b.BILL_NO, b.BILL_YEAR
	, vb.ADV_TAX
	, vb.ADV_TAX_BALANCE
	, td.ESTTAX
	, round(td.ESTTAX * vb.ADV_TAX_BALANCE / vb.ADV_TAX ,2) as SCHOOL_ESTTAX
from dy2014.dbo.vBILL_BALANCE vb 
inner join dy2014.dbo.bill b on b.BILL_NO=vb.BILL_NO and b.BILL_TYPE=vb.BILL_TYPE and b.BILL_YEAR=vb.BILL_YEAR and vb.ADV_TAX_BALANCE>0
inner join dy2014.dbo.property p on p.PROPERTYKEY=b.PROPERTYKEY 
inner join dy2014.dbo.TAXDETAIL td on td.PROPERTYKEY=b.PROPERTYKEY and td.TAXTYPE=3	
where b.bill_type IN ('R','P')
	and b.BALANCE_DUE > 0
	and isnull(b.UTILITY,0)=0
	and isnull(p.NOD,0)=0
) q group by bill_year

union all 

select bill_year, sum(school_esttax) from (
select b.BILL_NO, b.BILL_YEAR
	, vb.ADV_TAX
	, vb.ADV_TAX_BALANCE
	, td.ESTTAX
	, round(td.ESTTAX * vb.ADV_TAX_BALANCE / vb.ADV_TAX ,2) as SCHOOL_ESTTAX
from dy2013.dbo.vBILL_BALANCE vb 
inner join dy2013.dbo.bill b on b.BILL_NO=vb.BILL_NO and b.BILL_TYPE=vb.BILL_TYPE and b.BILL_YEAR=vb.BILL_YEAR and vb.ADV_TAX_BALANCE>0
inner join dy2013.dbo.property p on p.PROPERTYKEY=b.PROPERTYKEY 
inner join dy2013.dbo.TAXDETAIL td on td.PROPERTYKEY=b.PROPERTYKEY and td.TAXTYPE=3	
where b.bill_type IN ('R','P')
	and b.BALANCE_DUE > 0
	and isnull(b.UTILITY,0)=0
	and isnull(p.NOD,0)=0
) q group by bill_year

union all 

select bill_year, sum(school_esttax) from (
select b.BILL_NO, b.BILL_YEAR
	, vb.ADV_TAX
	, vb.ADV_TAX_BALANCE
	, td.ESTTAX
	, round(td.ESTTAX * vb.ADV_TAX_BALANCE / vb.ADV_TAX ,2) as SCHOOL_ESTTAX
from dy2012.dbo.vBILL_BALANCE vb 
inner join dy2012.dbo.bill b on b.BILL_NO=vb.BILL_NO and b.BILL_TYPE=vb.BILL_TYPE and b.BILL_YEAR=vb.BILL_YEAR and vb.ADV_TAX_BALANCE>0
inner join dy2012.dbo.property p on p.PROPERTYKEY=b.PROPERTYKEY 
inner join dy2012.dbo.TAXDETAIL td on td.PROPERTYKEY=b.PROPERTYKEY and td.TAXTYPE=3	
where b.bill_type IN ('R','P')
	and b.BALANCE_DUE > 0
	and isnull(b.UTILITY,0)=0
	and isnull(p.NOD,0)=0
) q group by bill_year

) z 

union all 

-- MOBILE

select 'MH' as PROPTYPE, sum(SCHOOL_ESTTAX) as SCHOOL_ESTTAX from (
select bill_year, sum(school_esttax) as SCHOOL_ESTTAX from (
select b.BILL_NO, b.BILL_YEAR
	, vb.ADV_TAX
	, vb.ADV_TAX_BALANCE
	, td.ESTTAX
	, round(td.ESTTAX * vb.ADV_TAX_BALANCE / vb.ADV_TAX ,2) as SCHOOL_ESTTAX
from dy2022.dbo.vBILL_BALANCE vb 
inner join dy2022.dbo.bill b on b.BILL_NO=vb.BILL_NO and b.BILL_TYPE=vb.BILL_TYPE and b.BILL_YEAR=vb.BILL_YEAR and vb.ADV_TAX_BALANCE>0
inner join dy2022.dbo.property p on p.PROPERTYKEY=b.PROPERTYKEY 
inner join dy2022.dbo.TAXDETAIL td on td.PROPERTYKEY=b.PROPERTYKEY and td.TAXTYPE=3	
where b.bill_type IN ('M')
	and b.BALANCE_DUE > 0
) q group by bill_year

union all 

select bill_year, sum(school_esttax) from (
select b.BILL_NO, b.BILL_YEAR
	, vb.ADV_TAX
	, vb.ADV_TAX_BALANCE
	, td.ESTTAX
	, round(td.ESTTAX * vb.ADV_TAX_BALANCE / vb.ADV_TAX ,2) as SCHOOL_ESTTAX
from dy2021.dbo.vBILL_BALANCE vb 
inner join dy2021.dbo.bill b on b.BILL_NO=vb.BILL_NO and b.BILL_TYPE=vb.BILL_TYPE and b.BILL_YEAR=vb.BILL_YEAR and vb.ADV_TAX_BALANCE>0
inner join dy2021.dbo.property p on p.PROPERTYKEY=b.PROPERTYKEY 
inner join dy2021.dbo.TAXDETAIL td on td.PROPERTYKEY=b.PROPERTYKEY and td.TAXTYPE=3	
where b.bill_type IN ('M')
	and b.BALANCE_DUE > 0
) q group by bill_year

union all 

select bill_year, sum(school_esttax) from (
select b.BILL_NO, b.BILL_YEAR
	, vb.ADV_TAX
	, vb.ADV_TAX_BALANCE
	, td.ESTTAX
	, round(td.ESTTAX * vb.ADV_TAX_BALANCE / vb.ADV_TAX ,2) as SCHOOL_ESTTAX
from dy2020.dbo.vBILL_BALANCE vb 
inner join dy2020.dbo.bill b on b.BILL_NO=vb.BILL_NO and b.BILL_TYPE=vb.BILL_TYPE and b.BILL_YEAR=vb.BILL_YEAR and vb.ADV_TAX_BALANCE>0
inner join dy2020.dbo.property p on p.PROPERTYKEY=b.PROPERTYKEY 
inner join dy2020.dbo.TAXDETAIL td on td.PROPERTYKEY=b.PROPERTYKEY and td.TAXTYPE=3	
where b.bill_type IN ('M')
	and b.BALANCE_DUE > 0
) q group by bill_year

union all 

select bill_year, sum(school_esttax) from (
select b.BILL_NO, b.BILL_YEAR
	, vb.ADV_TAX
	, vb.ADV_TAX_BALANCE
	, td.ESTTAX
	, round(td.ESTTAX * vb.ADV_TAX_BALANCE / vb.ADV_TAX ,2) as SCHOOL_ESTTAX
from dy2019.dbo.vBILL_BALANCE vb 
inner join dy2019.dbo.bill b on b.BILL_NO=vb.BILL_NO and b.BILL_TYPE=vb.BILL_TYPE and b.BILL_YEAR=vb.BILL_YEAR and vb.ADV_TAX_BALANCE>0
inner join dy2019.dbo.property p on p.PROPERTYKEY=b.PROPERTYKEY 
inner join dy2019.dbo.TAXDETAIL td on td.PROPERTYKEY=b.PROPERTYKEY and td.TAXTYPE=3	
where b.bill_type IN ('M')
	and b.BALANCE_DUE > 0
) q group by bill_year

union all 

select bill_year, sum(school_esttax) from (
select b.BILL_NO, b.BILL_YEAR
	, vb.ADV_TAX
	, vb.ADV_TAX_BALANCE
	, td.ESTTAX
	, round(td.ESTTAX * vb.ADV_TAX_BALANCE / vb.ADV_TAX ,2) as SCHOOL_ESTTAX
from dy2018.dbo.vBILL_BALANCE vb 
inner join dy2018.dbo.bill b on b.BILL_NO=vb.BILL_NO and b.BILL_TYPE=vb.BILL_TYPE and b.BILL_YEAR=vb.BILL_YEAR and vb.ADV_TAX_BALANCE>0
inner join dy2018.dbo.property p on p.PROPERTYKEY=b.PROPERTYKEY 
inner join dy2018.dbo.TAXDETAIL td on td.PROPERTYKEY=b.PROPERTYKEY and td.TAXTYPE=3	
where b.bill_type IN ('M')
	and b.BALANCE_DUE > 0
) q group by bill_year

union all 

select bill_year, sum(school_esttax) from (
select b.BILL_NO, b.BILL_YEAR
	, vb.ADV_TAX
	, vb.ADV_TAX_BALANCE
	, td.ESTTAX
	, round(td.ESTTAX * vb.ADV_TAX_BALANCE / vb.ADV_TAX ,2) as SCHOOL_ESTTAX
from dy2017.dbo.vBILL_BALANCE vb 
inner join dy2017.dbo.bill b on b.BILL_NO=vb.BILL_NO and b.BILL_TYPE=vb.BILL_TYPE and b.BILL_YEAR=vb.BILL_YEAR and vb.ADV_TAX_BALANCE>0
inner join dy2017.dbo.property p on p.PROPERTYKEY=b.PROPERTYKEY 
inner join dy2017.dbo.TAXDETAIL td on td.PROPERTYKEY=b.PROPERTYKEY and td.TAXTYPE=3	
where b.bill_type IN ('M')
	and b.BALANCE_DUE > 0
) q group by bill_year

union all 

select bill_year, sum(school_esttax) from (
select b.BILL_NO, b.BILL_YEAR
	, vb.ADV_TAX
	, vb.ADV_TAX_BALANCE
	, td.ESTTAX
	, round(td.ESTTAX * vb.ADV_TAX_BALANCE / vb.ADV_TAX ,2) as SCHOOL_ESTTAX
from dy2016.dbo.vBILL_BALANCE vb 
inner join dy2016.dbo.bill b on b.BILL_NO=vb.BILL_NO and b.BILL_TYPE=vb.BILL_TYPE and b.BILL_YEAR=vb.BILL_YEAR and vb.ADV_TAX_BALANCE>0
inner join dy2016.dbo.property p on p.PROPERTYKEY=b.PROPERTYKEY 
inner join dy2016.dbo.TAXDETAIL td on td.PROPERTYKEY=b.PROPERTYKEY and td.TAXTYPE=3	
where b.bill_type IN ('M')
	and b.BALANCE_DUE > 0
) q group by bill_year

union all 

select bill_year, sum(school_esttax) from (
select b.BILL_NO, b.BILL_YEAR
	, vb.ADV_TAX
	, vb.ADV_TAX_BALANCE
	, td.ESTTAX
	, round(td.ESTTAX * vb.ADV_TAX_BALANCE / vb.ADV_TAX ,2) as SCHOOL_ESTTAX
from dy2015.dbo.vBILL_BALANCE vb 
inner join dy2015.dbo.bill b on b.BILL_NO=vb.BILL_NO and b.BILL_TYPE=vb.BILL_TYPE and b.BILL_YEAR=vb.BILL_YEAR and vb.ADV_TAX_BALANCE>0
inner join dy2015.dbo.property p on p.PROPERTYKEY=b.PROPERTYKEY 
inner join dy2015.dbo.TAXDETAIL td on td.PROPERTYKEY=b.PROPERTYKEY and td.TAXTYPE=3	
where b.bill_type IN ('M')
	and b.BALANCE_DUE > 0
) q group by bill_year

union all 

select bill_year, sum(school_esttax) from (
select b.BILL_NO, b.BILL_YEAR
	, vb.ADV_TAX
	, vb.ADV_TAX_BALANCE
	, td.ESTTAX
	, round(td.ESTTAX * vb.ADV_TAX_BALANCE / vb.ADV_TAX ,2) as SCHOOL_ESTTAX
from dy2014.dbo.vBILL_BALANCE vb 
inner join dy2014.dbo.bill b on b.BILL_NO=vb.BILL_NO and b.BILL_TYPE=vb.BILL_TYPE and b.BILL_YEAR=vb.BILL_YEAR and vb.ADV_TAX_BALANCE>0
inner join dy2014.dbo.property p on p.PROPERTYKEY=b.PROPERTYKEY 
inner join dy2014.dbo.TAXDETAIL td on td.PROPERTYKEY=b.PROPERTYKEY and td.TAXTYPE=3	
where b.bill_type IN ('M')
	and b.BALANCE_DUE > 0
) q group by bill_year

union all 

select bill_year, sum(school_esttax) from (
select b.BILL_NO, b.BILL_YEAR
	, vb.ADV_TAX
	, vb.ADV_TAX_BALANCE
	, td.ESTTAX
	, round(td.ESTTAX * vb.ADV_TAX_BALANCE / vb.ADV_TAX ,2) as SCHOOL_ESTTAX
from dy2013.dbo.vBILL_BALANCE vb 
inner join dy2013.dbo.bill b on b.BILL_NO=vb.BILL_NO and b.BILL_TYPE=vb.BILL_TYPE and b.BILL_YEAR=vb.BILL_YEAR and vb.ADV_TAX_BALANCE>0
inner join dy2013.dbo.property p on p.PROPERTYKEY=b.PROPERTYKEY 
inner join dy2013.dbo.TAXDETAIL td on td.PROPERTYKEY=b.PROPERTYKEY and td.TAXTYPE=3	
where b.bill_type IN ('M')
	and b.BALANCE_DUE > 0
) q group by bill_year

union all 

select bill_year, sum(school_esttax) from (
select b.BILL_NO, b.BILL_YEAR
	, vb.ADV_TAX
	, vb.ADV_TAX_BALANCE
	, td.ESTTAX
	, round(td.ESTTAX * vb.ADV_TAX_BALANCE / vb.ADV_TAX ,2) as SCHOOL_ESTTAX
from dy2012.dbo.vBILL_BALANCE vb 
inner join dy2012.dbo.bill b on b.BILL_NO=vb.BILL_NO and b.BILL_TYPE=vb.BILL_TYPE and b.BILL_YEAR=vb.BILL_YEAR and vb.ADV_TAX_BALANCE>0
inner join dy2012.dbo.property p on p.PROPERTYKEY=b.PROPERTYKEY 
inner join dy2012.dbo.TAXDETAIL td on td.PROPERTYKEY=b.PROPERTYKEY and td.TAXTYPE=3	
where b.bill_type IN ('M')
	and b.BALANCE_DUE > 0
) q group by bill_year

) z 

union all 

-- HDE

select 'HDE' as PROPTYPE, sum(SCHOOL_ESTTAX) as SCHOOL_ESTTAX from (
select bill_year, sum(school_esttax) as SCHOOL_ESTTAX from (
select b.BILL_NO, b.BILL_YEAR
	, vb.ADV_TAX
	, vb.ADV_TAX_BALANCE
	, td.ESTTAX
	, round(td.ESTTAX * vb.ADV_TAX_BALANCE / vb.ADV_TAX ,2) as SCHOOL_ESTTAX
from dy2022.dbo.vBILL_BALANCE vb 
inner join dy2022.dbo.bill b on b.BILL_NO=vb.BILL_NO and b.BILL_TYPE=vb.BILL_TYPE and b.BILL_YEAR=vb.BILL_YEAR and vb.ADV_TAX_BALANCE>0
inner join dy2022.dbo.property p on p.PROPERTYKEY=b.PROPERTYKEY 
inner join dy2022.dbo.TAXDETAIL td on td.PROPERTYKEY=b.PROPERTYKEY and td.TAXTYPE=3	
where b.bill_type IN ('H')
	and b.BALANCE_DUE > 0
) q group by bill_year

union all 

select bill_year, sum(school_esttax) from (
select b.BILL_NO, b.BILL_YEAR
	, vb.ADV_TAX
	, vb.ADV_TAX_BALANCE
	, td.ESTTAX
	, round(td.ESTTAX * vb.ADV_TAX_BALANCE / vb.ADV_TAX ,2) as SCHOOL_ESTTAX
from dy2021.dbo.vBILL_BALANCE vb 
inner join dy2021.dbo.bill b on b.BILL_NO=vb.BILL_NO and b.BILL_TYPE=vb.BILL_TYPE and b.BILL_YEAR=vb.BILL_YEAR and vb.ADV_TAX_BALANCE>0
inner join dy2021.dbo.property p on p.PROPERTYKEY=b.PROPERTYKEY 
inner join dy2021.dbo.TAXDETAIL td on td.PROPERTYKEY=b.PROPERTYKEY and td.TAXTYPE=3	
where b.bill_type IN ('H')
	and b.BALANCE_DUE > 0
) q group by bill_year

union all 

select bill_year, sum(school_esttax) from (
select b.BILL_NO, b.BILL_YEAR
	, vb.ADV_TAX
	, vb.ADV_TAX_BALANCE
	, td.ESTTAX
	, round(td.ESTTAX * vb.ADV_TAX_BALANCE / vb.ADV_TAX ,2) as SCHOOL_ESTTAX
from dy2020.dbo.vBILL_BALANCE vb 
inner join dy2020.dbo.bill b on b.BILL_NO=vb.BILL_NO and b.BILL_TYPE=vb.BILL_TYPE and b.BILL_YEAR=vb.BILL_YEAR and vb.ADV_TAX_BALANCE>0
inner join dy2020.dbo.property p on p.PROPERTYKEY=b.PROPERTYKEY 
inner join dy2020.dbo.TAXDETAIL td on td.PROPERTYKEY=b.PROPERTYKEY and td.TAXTYPE=3	
where b.bill_type IN ('H')
	and b.BALANCE_DUE > 0
) q group by bill_year

union all 

select bill_year, sum(school_esttax) from (
select b.BILL_NO, b.BILL_YEAR
	, vb.ADV_TAX
	, vb.ADV_TAX_BALANCE
	, td.ESTTAX
	, round(td.ESTTAX * vb.ADV_TAX_BALANCE / vb.ADV_TAX ,2) as SCHOOL_ESTTAX
from dy2019.dbo.vBILL_BALANCE vb 
inner join dy2019.dbo.bill b on b.BILL_NO=vb.BILL_NO and b.BILL_TYPE=vb.BILL_TYPE and b.BILL_YEAR=vb.BILL_YEAR and vb.ADV_TAX_BALANCE>0
inner join dy2019.dbo.property p on p.PROPERTYKEY=b.PROPERTYKEY 
inner join dy2019.dbo.TAXDETAIL td on td.PROPERTYKEY=b.PROPERTYKEY and td.TAXTYPE=3	
where b.bill_type IN ('H')
	and b.BALANCE_DUE > 0
) q group by bill_year

union all 

select bill_year, sum(school_esttax) from (
select b.BILL_NO, b.BILL_YEAR
	, vb.ADV_TAX
	, vb.ADV_TAX_BALANCE
	, td.ESTTAX
	, round(td.ESTTAX * vb.ADV_TAX_BALANCE / vb.ADV_TAX ,2) as SCHOOL_ESTTAX
from dy2018.dbo.vBILL_BALANCE vb 
inner join dy2018.dbo.bill b on b.BILL_NO=vb.BILL_NO and b.BILL_TYPE=vb.BILL_TYPE and b.BILL_YEAR=vb.BILL_YEAR and vb.ADV_TAX_BALANCE>0
inner join dy2018.dbo.property p on p.PROPERTYKEY=b.PROPERTYKEY 
inner join dy2018.dbo.TAXDETAIL td on td.PROPERTYKEY=b.PROPERTYKEY and td.TAXTYPE=3	
where b.bill_type IN ('H')
	and b.BALANCE_DUE > 0
) q group by bill_year

union all 

select bill_year, sum(school_esttax) from (
select b.BILL_NO, b.BILL_YEAR
	, vb.ADV_TAX
	, vb.ADV_TAX_BALANCE
	, td.ESTTAX
	, round(td.ESTTAX * vb.ADV_TAX_BALANCE / vb.ADV_TAX ,2) as SCHOOL_ESTTAX
from dy2017.dbo.vBILL_BALANCE vb 
inner join dy2017.dbo.bill b on b.BILL_NO=vb.BILL_NO and b.BILL_TYPE=vb.BILL_TYPE and b.BILL_YEAR=vb.BILL_YEAR and vb.ADV_TAX_BALANCE>0
inner join dy2017.dbo.property p on p.PROPERTYKEY=b.PROPERTYKEY 
inner join dy2017.dbo.TAXDETAIL td on td.PROPERTYKEY=b.PROPERTYKEY and td.TAXTYPE=3	
where b.bill_type IN ('H')
	and b.BALANCE_DUE > 0
) q group by bill_year

union all 

select bill_year, sum(school_esttax) from (
select b.BILL_NO, b.BILL_YEAR
	, vb.ADV_TAX
	, vb.ADV_TAX_BALANCE
	, td.ESTTAX
	, round(td.ESTTAX * vb.ADV_TAX_BALANCE / vb.ADV_TAX ,2) as SCHOOL_ESTTAX
from dy2016.dbo.vBILL_BALANCE vb 
inner join dy2016.dbo.bill b on b.BILL_NO=vb.BILL_NO and b.BILL_TYPE=vb.BILL_TYPE and b.BILL_YEAR=vb.BILL_YEAR and vb.ADV_TAX_BALANCE>0
inner join dy2016.dbo.property p on p.PROPERTYKEY=b.PROPERTYKEY 
inner join dy2016.dbo.TAXDETAIL td on td.PROPERTYKEY=b.PROPERTYKEY and td.TAXTYPE=3	
where b.bill_type IN ('H')
	and b.BALANCE_DUE > 0
) q group by bill_year

union all 

select bill_year, sum(school_esttax) from (
select b.BILL_NO, b.BILL_YEAR
	, vb.ADV_TAX
	, vb.ADV_TAX_BALANCE
	, td.ESTTAX
	, round(td.ESTTAX * vb.ADV_TAX_BALANCE / vb.ADV_TAX ,2) as SCHOOL_ESTTAX
from dy2015.dbo.vBILL_BALANCE vb 
inner join dy2015.dbo.bill b on b.BILL_NO=vb.BILL_NO and b.BILL_TYPE=vb.BILL_TYPE and b.BILL_YEAR=vb.BILL_YEAR and vb.ADV_TAX_BALANCE>0
inner join dy2015.dbo.property p on p.PROPERTYKEY=b.PROPERTYKEY 
inner join dy2015.dbo.TAXDETAIL td on td.PROPERTYKEY=b.PROPERTYKEY and td.TAXTYPE=3	
where b.bill_type IN ('H')
	and b.BALANCE_DUE > 0
) q group by bill_year

union all 

select bill_year, sum(school_esttax) from (
select b.BILL_NO, b.BILL_YEAR
	, vb.ADV_TAX
	, vb.ADV_TAX_BALANCE
	, td.ESTTAX
	, round(td.ESTTAX * vb.ADV_TAX_BALANCE / vb.ADV_TAX ,2) as SCHOOL_ESTTAX
from dy2014.dbo.vBILL_BALANCE vb 
inner join dy2014.dbo.bill b on b.BILL_NO=vb.BILL_NO and b.BILL_TYPE=vb.BILL_TYPE and b.BILL_YEAR=vb.BILL_YEAR and vb.ADV_TAX_BALANCE>0
inner join dy2014.dbo.property p on p.PROPERTYKEY=b.PROPERTYKEY 
inner join dy2014.dbo.TAXDETAIL td on td.PROPERTYKEY=b.PROPERTYKEY and td.TAXTYPE=3	
where b.bill_type IN ('H')
	and b.BALANCE_DUE > 0
) q group by bill_year

union all 

select bill_year, sum(school_esttax) from (
select b.BILL_NO, b.BILL_YEAR
	, vb.ADV_TAX
	, vb.ADV_TAX_BALANCE
	, td.ESTTAX
	, round(td.ESTTAX * vb.ADV_TAX_BALANCE / vb.ADV_TAX ,2) as SCHOOL_ESTTAX
from dy2013.dbo.vBILL_BALANCE vb 
inner join dy2013.dbo.bill b on b.BILL_NO=vb.BILL_NO and b.BILL_TYPE=vb.BILL_TYPE and b.BILL_YEAR=vb.BILL_YEAR and vb.ADV_TAX_BALANCE>0
inner join dy2013.dbo.property p on p.PROPERTYKEY=b.PROPERTYKEY 
inner join dy2013.dbo.TAXDETAIL td on td.PROPERTYKEY=b.PROPERTYKEY and td.TAXTYPE=3	
where b.bill_type IN ('M')
	and b.BALANCE_DUE > 0
) q group by bill_year

union all 

select bill_year, sum(school_esttax) from (
select b.BILL_NO, b.BILL_YEAR
	, vb.ADV_TAX
	, vb.ADV_TAX_BALANCE
	, td.ESTTAX
	, round(td.ESTTAX * vb.ADV_TAX_BALANCE / vb.ADV_TAX ,2) as SCHOOL_ESTTAX
from dy2012.dbo.vBILL_BALANCE vb 
inner join dy2012.dbo.bill b on b.BILL_NO=vb.BILL_NO and b.BILL_TYPE=vb.BILL_TYPE and b.BILL_YEAR=vb.BILL_YEAR and vb.ADV_TAX_BALANCE>0
inner join dy2012.dbo.property p on p.PROPERTYKEY=b.PROPERTYKEY 
inner join dy2012.dbo.TAXDETAIL td on td.PROPERTYKEY=b.PROPERTYKEY and td.TAXTYPE=3	
where b.bill_type IN ('M')
	and b.BALANCE_DUE > 0
) q group by bill_year

) z 

union all 

-- PU

select 'PUBLIC UTILITY' as PROPTYPE, sum(SCHOOL_ESTTAX) as SCHOOL_ESTTAX from (
select bill_year, sum(school_esttax) as SCHOOL_ESTTAX from (
select b.BILL_NO, b.BILL_YEAR
	, vb.ADV_TAX
	, vb.ADV_TAX_BALANCE
	, td.ESTTAX
	, round(td.ESTTAX * vb.ADV_TAX_BALANCE / vb.ADV_TAX ,2) as SCHOOL_ESTTAX
from dy2022.dbo.vBILL_BALANCE vb 
inner join dy2022.dbo.bill b on b.BILL_NO=vb.BILL_NO and b.BILL_TYPE=vb.BILL_TYPE and b.BILL_YEAR=vb.BILL_YEAR and vb.ADV_TAX_BALANCE>0
inner join dy2022.dbo.property p on p.PROPERTYKEY=b.PROPERTYKEY 
inner join dy2022.dbo.TAXDETAIL td on td.PROPERTYKEY=b.PROPERTYKEY and td.TAXTYPE=3	
where b.bill_type IN ('R','P') 
	and b.BALANCE_DUE > 0
	and isnull(b.UTILITY,0)=1
) q group by bill_year

union all 

select bill_year, sum(school_esttax) from (
select b.BILL_NO, b.BILL_YEAR
	, vb.ADV_TAX
	, vb.ADV_TAX_BALANCE
	, td.ESTTAX
	, round(td.ESTTAX * vb.ADV_TAX_BALANCE / vb.ADV_TAX ,2) as SCHOOL_ESTTAX
from dy2021.dbo.vBILL_BALANCE vb 
inner join dy2021.dbo.bill b on b.BILL_NO=vb.BILL_NO and b.BILL_TYPE=vb.BILL_TYPE and b.BILL_YEAR=vb.BILL_YEAR and vb.ADV_TAX_BALANCE>0
inner join dy2021.dbo.property p on p.PROPERTYKEY=b.PROPERTYKEY 
inner join dy2021.dbo.TAXDETAIL td on td.PROPERTYKEY=b.PROPERTYKEY and td.TAXTYPE=3	
where b.bill_type IN ('R','P')
	and b.BALANCE_DUE > 0
	and isnull(b.UTILITY,0)=1
) q group by bill_year

union all 

select bill_year, sum(school_esttax) from (
select b.BILL_NO, b.BILL_YEAR
	, vb.ADV_TAX
	, vb.ADV_TAX_BALANCE
	, td.ESTTAX
	, round(td.ESTTAX * vb.ADV_TAX_BALANCE / vb.ADV_TAX ,2) as SCHOOL_ESTTAX
from dy2020.dbo.vBILL_BALANCE vb 
inner join dy2020.dbo.bill b on b.BILL_NO=vb.BILL_NO and b.BILL_TYPE=vb.BILL_TYPE and b.BILL_YEAR=vb.BILL_YEAR and vb.ADV_TAX_BALANCE>0
inner join dy2020.dbo.property p on p.PROPERTYKEY=b.PROPERTYKEY 
inner join dy2020.dbo.TAXDETAIL td on td.PROPERTYKEY=b.PROPERTYKEY and td.TAXTYPE=3	
where b.bill_type IN ('R','P')
	and b.BALANCE_DUE > 0
	and isnull(b.UTILITY,0)=1
) q group by bill_year

union all 

select bill_year, sum(school_esttax) from (
select b.BILL_NO, b.BILL_YEAR
	, vb.ADV_TAX
	, vb.ADV_TAX_BALANCE
	, td.ESTTAX
	, round(td.ESTTAX * vb.ADV_TAX_BALANCE / vb.ADV_TAX ,2) as SCHOOL_ESTTAX
from dy2019.dbo.vBILL_BALANCE vb 
inner join dy2019.dbo.bill b on b.BILL_NO=vb.BILL_NO and b.BILL_TYPE=vb.BILL_TYPE and b.BILL_YEAR=vb.BILL_YEAR and vb.ADV_TAX_BALANCE>0
inner join dy2019.dbo.property p on p.PROPERTYKEY=b.PROPERTYKEY 
inner join dy2019.dbo.TAXDETAIL td on td.PROPERTYKEY=b.PROPERTYKEY and td.TAXTYPE=3	
where b.bill_type IN ('R','P')
	and b.BALANCE_DUE > 0
	and isnull(b.UTILITY,0)=1
) q group by bill_year

union all 

select bill_year, sum(school_esttax) from (
select b.BILL_NO, b.BILL_YEAR
	, vb.ADV_TAX
	, vb.ADV_TAX_BALANCE
	, td.ESTTAX
	, round(td.ESTTAX * vb.ADV_TAX_BALANCE / vb.ADV_TAX ,2) as SCHOOL_ESTTAX
from dy2018.dbo.vBILL_BALANCE vb 
inner join dy2018.dbo.bill b on b.BILL_NO=vb.BILL_NO and b.BILL_TYPE=vb.BILL_TYPE and b.BILL_YEAR=vb.BILL_YEAR and vb.ADV_TAX_BALANCE>0
inner join dy2018.dbo.property p on p.PROPERTYKEY=b.PROPERTYKEY 
inner join dy2018.dbo.TAXDETAIL td on td.PROPERTYKEY=b.PROPERTYKEY and td.TAXTYPE=3	
where b.bill_type IN ('R','P')
	and b.BALANCE_DUE > 0
	and isnull(b.UTILITY,0)=1
) q group by bill_year

union all 

select bill_year, sum(school_esttax) from (
select b.BILL_NO, b.BILL_YEAR
	, vb.ADV_TAX
	, vb.ADV_TAX_BALANCE
	, td.ESTTAX
	, round(td.ESTTAX * vb.ADV_TAX_BALANCE / vb.ADV_TAX ,2) as SCHOOL_ESTTAX
from dy2017.dbo.vBILL_BALANCE vb 
inner join dy2017.dbo.bill b on b.BILL_NO=vb.BILL_NO and b.BILL_TYPE=vb.BILL_TYPE and b.BILL_YEAR=vb.BILL_YEAR and vb.ADV_TAX_BALANCE>0
inner join dy2017.dbo.property p on p.PROPERTYKEY=b.PROPERTYKEY 
inner join dy2017.dbo.TAXDETAIL td on td.PROPERTYKEY=b.PROPERTYKEY and td.TAXTYPE=3	
where b.bill_type IN ('R','P')
	and b.BALANCE_DUE > 0
	and isnull(b.UTILITY,0)=1
) q group by bill_year

union all 

select bill_year, sum(school_esttax) from (
select b.BILL_NO, b.BILL_YEAR
	, vb.ADV_TAX
	, vb.ADV_TAX_BALANCE
	, td.ESTTAX
	, round(td.ESTTAX * vb.ADV_TAX_BALANCE / vb.ADV_TAX ,2) as SCHOOL_ESTTAX
from dy2016.dbo.vBILL_BALANCE vb 
inner join dy2016.dbo.bill b on b.BILL_NO=vb.BILL_NO and b.BILL_TYPE=vb.BILL_TYPE and b.BILL_YEAR=vb.BILL_YEAR and vb.ADV_TAX_BALANCE>0
inner join dy2016.dbo.property p on p.PROPERTYKEY=b.PROPERTYKEY 
inner join dy2016.dbo.TAXDETAIL td on td.PROPERTYKEY=b.PROPERTYKEY and td.TAXTYPE=3	
where b.bill_type IN ('R','P')
	and b.BALANCE_DUE > 0
	and isnull(b.UTILITY,0)=1
) q group by bill_year

union all 

select bill_year, sum(school_esttax) from (
select b.BILL_NO, b.BILL_YEAR
	, vb.ADV_TAX
	, vb.ADV_TAX_BALANCE
	, td.ESTTAX
	, round(td.ESTTAX * vb.ADV_TAX_BALANCE / vb.ADV_TAX ,2) as SCHOOL_ESTTAX
from dy2015.dbo.vBILL_BALANCE vb 
inner join dy2015.dbo.bill b on b.BILL_NO=vb.BILL_NO and b.BILL_TYPE=vb.BILL_TYPE and b.BILL_YEAR=vb.BILL_YEAR and vb.ADV_TAX_BALANCE>0
inner join dy2015.dbo.property p on p.PROPERTYKEY=b.PROPERTYKEY 
inner join dy2015.dbo.TAXDETAIL td on td.PROPERTYKEY=b.PROPERTYKEY and td.TAXTYPE=3	
where b.bill_type IN ('R','P')
	and b.BALANCE_DUE > 0
	and isnull(b.UTILITY,0)=1
) q group by bill_year

union all 

select bill_year, sum(school_esttax) from (
select b.BILL_NO, b.BILL_YEAR
	, vb.ADV_TAX
	, vb.ADV_TAX_BALANCE
	, td.ESTTAX
	, round(td.ESTTAX * vb.ADV_TAX_BALANCE / vb.ADV_TAX ,2) as SCHOOL_ESTTAX
from dy2014.dbo.vBILL_BALANCE vb 
inner join dy2014.dbo.bill b on b.BILL_NO=vb.BILL_NO and b.BILL_TYPE=vb.BILL_TYPE and b.BILL_YEAR=vb.BILL_YEAR and vb.ADV_TAX_BALANCE>0
inner join dy2014.dbo.property p on p.PROPERTYKEY=b.PROPERTYKEY 
inner join dy2014.dbo.TAXDETAIL td on td.PROPERTYKEY=b.PROPERTYKEY and td.TAXTYPE=3	
where b.bill_type IN ('R','P')
	and b.BALANCE_DUE > 0
	and isnull(b.UTILITY,0)=1
) q group by bill_year

union all 

select bill_year, sum(school_esttax) from (
select b.BILL_NO, b.BILL_YEAR
	, vb.ADV_TAX
	, vb.ADV_TAX_BALANCE
	, td.ESTTAX
	, round(td.ESTTAX * vb.ADV_TAX_BALANCE / vb.ADV_TAX ,2) as SCHOOL_ESTTAX
from dy2013.dbo.vBILL_BALANCE vb 
inner join dy2013.dbo.bill b on b.BILL_NO=vb.BILL_NO and b.BILL_TYPE=vb.BILL_TYPE and b.BILL_YEAR=vb.BILL_YEAR and vb.ADV_TAX_BALANCE>0
inner join dy2013.dbo.property p on p.PROPERTYKEY=b.PROPERTYKEY 
inner join dy2013.dbo.TAXDETAIL td on td.PROPERTYKEY=b.PROPERTYKEY and td.TAXTYPE=3	
where b.bill_type IN ('R','P')
	and b.BALANCE_DUE > 0
	and isnull(b.UTILITY,0)=1
) q group by bill_year

union all 

select bill_year, sum(school_esttax) from (
select b.BILL_NO, b.BILL_YEAR
	, vb.ADV_TAX
	, vb.ADV_TAX_BALANCE
	, td.ESTTAX
	, round(td.ESTTAX * vb.ADV_TAX_BALANCE / vb.ADV_TAX ,2) as SCHOOL_ESTTAX
from dy2012.dbo.vBILL_BALANCE vb 
inner join dy2012.dbo.bill b on b.BILL_NO=vb.BILL_NO and b.BILL_TYPE=vb.BILL_TYPE and b.BILL_YEAR=vb.BILL_YEAR and vb.ADV_TAX_BALANCE>0
inner join dy2012.dbo.property p on p.PROPERTYKEY=b.PROPERTYKEY 
inner join dy2012.dbo.TAXDETAIL td on td.PROPERTYKEY=b.PROPERTYKEY and td.TAXTYPE=3	
where b.bill_type IN ('R','P')
	and b.BALANCE_DUE > 0
	and isnull(b.UTILITY,0)=1
) q group by bill_year

) z 

union all 

-- TIMBER

select 'TIMBER' as PROPTYPE, sum(SCHOOL_ESTTAX) as SCHOOL_ESTTAX from (
select bill_year, sum(school_esttax) as SCHOOL_ESTTAX from (
select b.BILL_NO, b.BILL_YEAR
	, vb.ADV_TAX
	, vb.ADV_TAX_BALANCE
	, td.ESTTAX
	, round(td.ESTTAX * vb.ADV_TAX_BALANCE / vb.ADV_TAX ,2) as SCHOOL_ESTTAX
from dy2022.dbo.vBILL_BALANCE vb 
inner join dy2022.dbo.bill b on b.BILL_NO=vb.BILL_NO and b.BILL_TYPE=vb.BILL_TYPE and b.BILL_YEAR=vb.BILL_YEAR and vb.ADV_TAX_BALANCE>0
inner join dy2022.dbo.property p on p.PROPERTYKEY=b.PROPERTYKEY 
inner join dy2022.dbo.TAXDETAIL td on td.PROPERTYKEY=b.PROPERTYKEY and td.TAXTYPE=3	
where b.bill_type IN ('T')
	and b.BALANCE_DUE > 0
) q group by bill_year

union all 

select bill_year, sum(school_esttax) from (
select b.BILL_NO, b.BILL_YEAR
	, vb.ADV_TAX
	, vb.ADV_TAX_BALANCE
	, td.ESTTAX
	, round(td.ESTTAX * vb.ADV_TAX_BALANCE / vb.ADV_TAX ,2) as SCHOOL_ESTTAX
from dy2021.dbo.vBILL_BALANCE vb 
inner join dy2021.dbo.bill b on b.BILL_NO=vb.BILL_NO and b.BILL_TYPE=vb.BILL_TYPE and b.BILL_YEAR=vb.BILL_YEAR and vb.ADV_TAX_BALANCE>0
inner join dy2021.dbo.property p on p.PROPERTYKEY=b.PROPERTYKEY 
inner join dy2021.dbo.TAXDETAIL td on td.PROPERTYKEY=b.PROPERTYKEY and td.TAXTYPE=3	
where b.bill_type IN ('T')
	and b.BALANCE_DUE > 0
) q group by bill_year

union all 

select bill_year, sum(school_esttax) from (
select b.BILL_NO, b.BILL_YEAR
	, vb.ADV_TAX
	, vb.ADV_TAX_BALANCE
	, td.ESTTAX
	, round(td.ESTTAX * vb.ADV_TAX_BALANCE / vb.ADV_TAX ,2) as SCHOOL_ESTTAX
from dy2020.dbo.vBILL_BALANCE vb 
inner join dy2020.dbo.bill b on b.BILL_NO=vb.BILL_NO and b.BILL_TYPE=vb.BILL_TYPE and b.BILL_YEAR=vb.BILL_YEAR and vb.ADV_TAX_BALANCE>0
inner join dy2020.dbo.property p on p.PROPERTYKEY=b.PROPERTYKEY 
inner join dy2020.dbo.TAXDETAIL td on td.PROPERTYKEY=b.PROPERTYKEY and td.TAXTYPE=3	
where b.bill_type IN ('T')
	and b.BALANCE_DUE > 0
) q group by bill_year

union all 

select bill_year, sum(school_esttax) from (
select b.BILL_NO, b.BILL_YEAR
	, vb.ADV_TAX
	, vb.ADV_TAX_BALANCE
	, td.ESTTAX
	, round(td.ESTTAX * vb.ADV_TAX_BALANCE / vb.ADV_TAX ,2) as SCHOOL_ESTTAX
from dy2019.dbo.vBILL_BALANCE vb 
inner join dy2019.dbo.bill b on b.BILL_NO=vb.BILL_NO and b.BILL_TYPE=vb.BILL_TYPE and b.BILL_YEAR=vb.BILL_YEAR and vb.ADV_TAX_BALANCE>0
inner join dy2019.dbo.property p on p.PROPERTYKEY=b.PROPERTYKEY 
inner join dy2019.dbo.TAXDETAIL td on td.PROPERTYKEY=b.PROPERTYKEY and td.TAXTYPE=3	
where b.bill_type IN ('T')
	and b.BALANCE_DUE > 0
) q group by bill_year

union all 

select bill_year, sum(school_esttax) from (
select b.BILL_NO, b.BILL_YEAR
	, vb.ADV_TAX
	, vb.ADV_TAX_BALANCE
	, td.ESTTAX
	, round(td.ESTTAX * vb.ADV_TAX_BALANCE / vb.ADV_TAX ,2) as SCHOOL_ESTTAX
from dy2018.dbo.vBILL_BALANCE vb 
inner join dy2018.dbo.bill b on b.BILL_NO=vb.BILL_NO and b.BILL_TYPE=vb.BILL_TYPE and b.BILL_YEAR=vb.BILL_YEAR and vb.ADV_TAX_BALANCE>0
inner join dy2018.dbo.property p on p.PROPERTYKEY=b.PROPERTYKEY 
inner join dy2018.dbo.TAXDETAIL td on td.PROPERTYKEY=b.PROPERTYKEY and td.TAXTYPE=3	
where b.bill_type IN ('T')
	and b.BALANCE_DUE > 0
) q group by bill_year

union all 

select bill_year, sum(school_esttax) from (
select b.BILL_NO, b.BILL_YEAR
	, vb.ADV_TAX
	, vb.ADV_TAX_BALANCE
	, td.ESTTAX
	, round(td.ESTTAX * vb.ADV_TAX_BALANCE / vb.ADV_TAX ,2) as SCHOOL_ESTTAX
from dy2017.dbo.vBILL_BALANCE vb 
inner join dy2017.dbo.bill b on b.BILL_NO=vb.BILL_NO and b.BILL_TYPE=vb.BILL_TYPE and b.BILL_YEAR=vb.BILL_YEAR and vb.ADV_TAX_BALANCE>0
inner join dy2017.dbo.property p on p.PROPERTYKEY=b.PROPERTYKEY 
inner join dy2017.dbo.TAXDETAIL td on td.PROPERTYKEY=b.PROPERTYKEY and td.TAXTYPE=3	
where b.bill_type IN ('T')
	and b.BALANCE_DUE > 0
) q group by bill_year

union all 

select bill_year, sum(school_esttax) from (
select b.BILL_NO, b.BILL_YEAR
	, vb.ADV_TAX
	, vb.ADV_TAX_BALANCE
	, td.ESTTAX
	, round(td.ESTTAX * vb.ADV_TAX_BALANCE / vb.ADV_TAX ,2) as SCHOOL_ESTTAX
from dy2016.dbo.vBILL_BALANCE vb 
inner join dy2016.dbo.bill b on b.BILL_NO=vb.BILL_NO and b.BILL_TYPE=vb.BILL_TYPE and b.BILL_YEAR=vb.BILL_YEAR and vb.ADV_TAX_BALANCE>0
inner join dy2016.dbo.property p on p.PROPERTYKEY=b.PROPERTYKEY 
inner join dy2016.dbo.TAXDETAIL td on td.PROPERTYKEY=b.PROPERTYKEY and td.TAXTYPE=3	
where b.bill_type IN ('T')
	and b.BALANCE_DUE > 0
) q group by bill_year

union all 

select bill_year, sum(school_esttax) from (
select b.BILL_NO, b.BILL_YEAR
	, vb.ADV_TAX
	, vb.ADV_TAX_BALANCE
	, td.ESTTAX
	, round(td.ESTTAX * vb.ADV_TAX_BALANCE / vb.ADV_TAX ,2) as SCHOOL_ESTTAX
from dy2015.dbo.vBILL_BALANCE vb 
inner join dy2015.dbo.bill b on b.BILL_NO=vb.BILL_NO and b.BILL_TYPE=vb.BILL_TYPE and b.BILL_YEAR=vb.BILL_YEAR and vb.ADV_TAX_BALANCE>0
inner join dy2015.dbo.property p on p.PROPERTYKEY=b.PROPERTYKEY 
inner join dy2015.dbo.TAXDETAIL td on td.PROPERTYKEY=b.PROPERTYKEY and td.TAXTYPE=3	
where b.bill_type IN ('T')
	and b.BALANCE_DUE > 0
) q group by bill_year

union all 

select bill_year, sum(school_esttax) from (
select b.BILL_NO, b.BILL_YEAR
	, vb.ADV_TAX
	, vb.ADV_TAX_BALANCE
	, td.ESTTAX
	, round(td.ESTTAX * vb.ADV_TAX_BALANCE / vb.ADV_TAX ,2) as SCHOOL_ESTTAX
from dy2014.dbo.vBILL_BALANCE vb 
inner join dy2014.dbo.bill b on b.BILL_NO=vb.BILL_NO and b.BILL_TYPE=vb.BILL_TYPE and b.BILL_YEAR=vb.BILL_YEAR and vb.ADV_TAX_BALANCE>0
inner join dy2014.dbo.property p on p.PROPERTYKEY=b.PROPERTYKEY 
inner join dy2014.dbo.TAXDETAIL td on td.PROPERTYKEY=b.PROPERTYKEY and td.TAXTYPE=3	
where b.bill_type IN ('T')
	and b.BALANCE_DUE > 0
) q group by bill_year

union all 

select bill_year, sum(school_esttax) from (
select b.BILL_NO, b.BILL_YEAR
	, vb.ADV_TAX
	, vb.ADV_TAX_BALANCE
	, td.ESTTAX
	, round(td.ESTTAX * vb.ADV_TAX_BALANCE / vb.ADV_TAX ,2) as SCHOOL_ESTTAX
from dy2013.dbo.vBILL_BALANCE vb 
inner join dy2013.dbo.bill b on b.BILL_NO=vb.BILL_NO and b.BILL_TYPE=vb.BILL_TYPE and b.BILL_YEAR=vb.BILL_YEAR and vb.ADV_TAX_BALANCE>0
inner join dy2013.dbo.property p on p.PROPERTYKEY=b.PROPERTYKEY 
inner join dy2013.dbo.TAXDETAIL td on td.PROPERTYKEY=b.PROPERTYKEY and td.TAXTYPE=3	
where b.bill_type IN ('T')
	and b.BALANCE_DUE > 0
) q group by bill_year

union all 

select bill_year, sum(school_esttax) from (
select b.BILL_NO, b.BILL_YEAR
	, vb.ADV_TAX
	, vb.ADV_TAX_BALANCE
	, td.ESTTAX
	, round(td.ESTTAX * vb.ADV_TAX_BALANCE / vb.ADV_TAX ,2) as SCHOOL_ESTTAX
from dy2012.dbo.vBILL_BALANCE vb 
inner join dy2012.dbo.bill b on b.BILL_NO=vb.BILL_NO and b.BILL_TYPE=vb.BILL_TYPE and b.BILL_YEAR=vb.BILL_YEAR and vb.ADV_TAX_BALANCE>0
inner join dy2012.dbo.property p on p.PROPERTYKEY=b.PROPERTYKEY 
inner join dy2012.dbo.TAXDETAIL td on td.PROPERTYKEY=b.PROPERTYKEY and td.TAXTYPE=3	
where b.bill_type IN ('T')
	and b.BALANCE_DUE > 0
) q group by bill_year

) z 

union all 

-- NOD

select 'NOD' as PROPTYPE, sum(SCHOOL_ESTTAX) as SCHOOL_ESTTAX from (
select bill_year, sum(school_esttax) as SCHOOL_ESTTAX from (
select b.BILL_NO, b.BILL_YEAR
	, vb.ADV_TAX
	, vb.ADV_TAX_BALANCE
	, td.ESTTAX
	, round(td.ESTTAX * vb.ADV_TAX_BALANCE / vb.ADV_TAX ,2) as SCHOOL_ESTTAX
from dy2022.dbo.vBILL_BALANCE vb 
inner join dy2022.dbo.bill b on b.BILL_NO=vb.BILL_NO and b.BILL_TYPE=vb.BILL_TYPE and b.BILL_YEAR=vb.BILL_YEAR and vb.ADV_TAX_BALANCE>0
inner join dy2022.dbo.property p on p.PROPERTYKEY=b.PROPERTYKEY 
inner join dy2022.dbo.TAXDETAIL td on td.PROPERTYKEY=b.PROPERTYKEY and td.TAXTYPE=3	
where b.bill_type IN ('R','P') 
	and b.BALANCE_DUE > 0
	and isnull(b.UTILITY,0)=0
	and isnull(p.NOD,0)=1
) q group by bill_year

union all 

select bill_year, sum(school_esttax) from (
select b.BILL_NO, b.BILL_YEAR
	, vb.ADV_TAX
	, vb.ADV_TAX_BALANCE
	, td.ESTTAX
	, round(td.ESTTAX * vb.ADV_TAX_BALANCE / vb.ADV_TAX ,2) as SCHOOL_ESTTAX
from dy2021.dbo.vBILL_BALANCE vb 
inner join dy2021.dbo.bill b on b.BILL_NO=vb.BILL_NO and b.BILL_TYPE=vb.BILL_TYPE and b.BILL_YEAR=vb.BILL_YEAR and vb.ADV_TAX_BALANCE>0
inner join dy2021.dbo.property p on p.PROPERTYKEY=b.PROPERTYKEY 
inner join dy2021.dbo.TAXDETAIL td on td.PROPERTYKEY=b.PROPERTYKEY and td.TAXTYPE=3	
where b.bill_type IN ('R','P')
	and b.BALANCE_DUE > 0
	and isnull(b.UTILITY,0)=0
	and isnull(p.NOD,0)=1
) q group by bill_year

union all 

select bill_year, sum(school_esttax) from (
select b.BILL_NO, b.BILL_YEAR
	, vb.ADV_TAX
	, vb.ADV_TAX_BALANCE
	, td.ESTTAX
	, round(td.ESTTAX * vb.ADV_TAX_BALANCE / vb.ADV_TAX ,2) as SCHOOL_ESTTAX
from dy2020.dbo.vBILL_BALANCE vb 
inner join dy2020.dbo.bill b on b.BILL_NO=vb.BILL_NO and b.BILL_TYPE=vb.BILL_TYPE and b.BILL_YEAR=vb.BILL_YEAR and vb.ADV_TAX_BALANCE>0
inner join dy2020.dbo.property p on p.PROPERTYKEY=b.PROPERTYKEY 
inner join dy2020.dbo.TAXDETAIL td on td.PROPERTYKEY=b.PROPERTYKEY and td.TAXTYPE=3	
where b.bill_type IN ('R','P')
	and b.BALANCE_DUE > 0
	and isnull(b.UTILITY,0)=0
	and isnull(p.NOD,0)=1
) q group by bill_year

union all 

select bill_year, sum(school_esttax) from (
select b.BILL_NO, b.BILL_YEAR
	, vb.ADV_TAX
	, vb.ADV_TAX_BALANCE
	, td.ESTTAX
	, round(td.ESTTAX * vb.ADV_TAX_BALANCE / vb.ADV_TAX ,2) as SCHOOL_ESTTAX
from dy2019.dbo.vBILL_BALANCE vb 
inner join dy2019.dbo.bill b on b.BILL_NO=vb.BILL_NO and b.BILL_TYPE=vb.BILL_TYPE and b.BILL_YEAR=vb.BILL_YEAR and vb.ADV_TAX_BALANCE>0
inner join dy2019.dbo.property p on p.PROPERTYKEY=b.PROPERTYKEY 
inner join dy2019.dbo.TAXDETAIL td on td.PROPERTYKEY=b.PROPERTYKEY and td.TAXTYPE=3	
where b.bill_type IN ('R','P')
	and b.BALANCE_DUE > 0
	and isnull(b.UTILITY,0)=0
	and isnull(p.NOD,0)=1
) q group by bill_year

union all 

select bill_year, sum(school_esttax) from (
select b.BILL_NO, b.BILL_YEAR
	, vb.ADV_TAX
	, vb.ADV_TAX_BALANCE
	, td.ESTTAX
	, round(td.ESTTAX * vb.ADV_TAX_BALANCE / vb.ADV_TAX ,2) as SCHOOL_ESTTAX
from dy2018.dbo.vBILL_BALANCE vb 
inner join dy2018.dbo.bill b on b.BILL_NO=vb.BILL_NO and b.BILL_TYPE=vb.BILL_TYPE and b.BILL_YEAR=vb.BILL_YEAR and vb.ADV_TAX_BALANCE>0
inner join dy2018.dbo.property p on p.PROPERTYKEY=b.PROPERTYKEY 
inner join dy2018.dbo.TAXDETAIL td on td.PROPERTYKEY=b.PROPERTYKEY and td.TAXTYPE=3	
where b.bill_type IN ('R','P')
	and b.BALANCE_DUE > 0
	and isnull(b.UTILITY,0)=0
	and isnull(p.NOD,0)=1
) q group by bill_year

union all 

select bill_year, sum(school_esttax) from (
select b.BILL_NO, b.BILL_YEAR
	, vb.ADV_TAX
	, vb.ADV_TAX_BALANCE
	, td.ESTTAX
	, round(td.ESTTAX * vb.ADV_TAX_BALANCE / vb.ADV_TAX ,2) as SCHOOL_ESTTAX
from dy2017.dbo.vBILL_BALANCE vb 
inner join dy2017.dbo.bill b on b.BILL_NO=vb.BILL_NO and b.BILL_TYPE=vb.BILL_TYPE and b.BILL_YEAR=vb.BILL_YEAR and vb.ADV_TAX_BALANCE>0
inner join dy2017.dbo.property p on p.PROPERTYKEY=b.PROPERTYKEY 
inner join dy2017.dbo.TAXDETAIL td on td.PROPERTYKEY=b.PROPERTYKEY and td.TAXTYPE=3	
where b.bill_type IN ('R','P')
	and b.BALANCE_DUE > 0
	and isnull(b.UTILITY,0)=0
	and isnull(p.NOD,0)=1
) q group by bill_year

union all 

select bill_year, sum(school_esttax) from (
select b.BILL_NO, b.BILL_YEAR
	, vb.ADV_TAX
	, vb.ADV_TAX_BALANCE
	, td.ESTTAX
	, round(td.ESTTAX * vb.ADV_TAX_BALANCE / vb.ADV_TAX ,2) as SCHOOL_ESTTAX
from dy2016.dbo.vBILL_BALANCE vb 
inner join dy2016.dbo.bill b on b.BILL_NO=vb.BILL_NO and b.BILL_TYPE=vb.BILL_TYPE and b.BILL_YEAR=vb.BILL_YEAR and vb.ADV_TAX_BALANCE>0
inner join dy2016.dbo.property p on p.PROPERTYKEY=b.PROPERTYKEY 
inner join dy2016.dbo.TAXDETAIL td on td.PROPERTYKEY=b.PROPERTYKEY and td.TAXTYPE=3	
where b.bill_type IN ('R','P')
	and b.BALANCE_DUE > 0
	and isnull(b.UTILITY,0)=0
	and isnull(p.NOD,0)=1
) q group by bill_year

union all 

select bill_year, sum(school_esttax) from (
select b.BILL_NO, b.BILL_YEAR
	, vb.ADV_TAX
	, vb.ADV_TAX_BALANCE
	, td.ESTTAX
	, round(td.ESTTAX * vb.ADV_TAX_BALANCE / vb.ADV_TAX ,2) as SCHOOL_ESTTAX
from dy2015.dbo.vBILL_BALANCE vb 
inner join dy2015.dbo.bill b on b.BILL_NO=vb.BILL_NO and b.BILL_TYPE=vb.BILL_TYPE and b.BILL_YEAR=vb.BILL_YEAR and vb.ADV_TAX_BALANCE>0
inner join dy2015.dbo.property p on p.PROPERTYKEY=b.PROPERTYKEY 
inner join dy2015.dbo.TAXDETAIL td on td.PROPERTYKEY=b.PROPERTYKEY and td.TAXTYPE=3	
where b.bill_type IN ('R','P')
	and b.BALANCE_DUE > 0
	and isnull(b.UTILITY,0)=0
	and isnull(p.NOD,0)=1
) q group by bill_year

union all 

select bill_year, sum(school_esttax) from (
select b.BILL_NO, b.BILL_YEAR
	, vb.ADV_TAX
	, vb.ADV_TAX_BALANCE
	, td.ESTTAX
	, round(td.ESTTAX * vb.ADV_TAX_BALANCE / vb.ADV_TAX ,2) as SCHOOL_ESTTAX
from dy2014.dbo.vBILL_BALANCE vb 
inner join dy2014.dbo.bill b on b.BILL_NO=vb.BILL_NO and b.BILL_TYPE=vb.BILL_TYPE and b.BILL_YEAR=vb.BILL_YEAR and vb.ADV_TAX_BALANCE>0
inner join dy2014.dbo.property p on p.PROPERTYKEY=b.PROPERTYKEY 
inner join dy2014.dbo.TAXDETAIL td on td.PROPERTYKEY=b.PROPERTYKEY and td.TAXTYPE=3	
where b.bill_type IN ('R','P')
	and b.BALANCE_DUE > 0
	and isnull(b.UTILITY,0)=0
	and isnull(p.NOD,0)=1
) q group by bill_year

union all 

select bill_year, sum(school_esttax) from (
select b.BILL_NO, b.BILL_YEAR
	, vb.ADV_TAX
	, vb.ADV_TAX_BALANCE
	, td.ESTTAX
	, round(td.ESTTAX * vb.ADV_TAX_BALANCE / vb.ADV_TAX ,2) as SCHOOL_ESTTAX
from dy2013.dbo.vBILL_BALANCE vb 
inner join dy2013.dbo.bill b on b.BILL_NO=vb.BILL_NO and b.BILL_TYPE=vb.BILL_TYPE and b.BILL_YEAR=vb.BILL_YEAR and vb.ADV_TAX_BALANCE>0
inner join dy2013.dbo.property p on p.PROPERTYKEY=b.PROPERTYKEY 
inner join dy2013.dbo.TAXDETAIL td on td.PROPERTYKEY=b.PROPERTYKEY and td.TAXTYPE=3	
where b.bill_type IN ('R','P')
	and b.BALANCE_DUE > 0
	and isnull(b.UTILITY,0)=0
	and isnull(p.NOD,0)=1
) q group by bill_year

union all 

select bill_year, sum(school_esttax) from (
select b.BILL_NO, b.BILL_YEAR
	, vb.ADV_TAX
	, vb.ADV_TAX_BALANCE
	, td.ESTTAX
	, round(td.ESTTAX * vb.ADV_TAX_BALANCE / vb.ADV_TAX ,2) as SCHOOL_ESTTAX
from dy2012.dbo.vBILL_BALANCE vb 
inner join dy2012.dbo.bill b on b.BILL_NO=vb.BILL_NO and b.BILL_TYPE=vb.BILL_TYPE and b.BILL_YEAR=vb.BILL_YEAR and vb.ADV_TAX_BALANCE>0
inner join dy2012.dbo.property p on p.PROPERTYKEY=b.PROPERTYKEY 
inner join dy2012.dbo.TAXDETAIL td on td.PROPERTYKEY=b.PROPERTYKEY and td.TAXTYPE=3	
where b.bill_type IN ('R','P')
	and b.BALANCE_DUE > 0
	and isnull(b.UTILITY,0)=0
	and isnull(p.NOD,0)=1
) q group by bill_year

) z