--declare @proptype as char(1) = 't'

select bill_year, sum(SCHOOL_ESTTAX) from (
select bill_year, sum(school_esttax) 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 (select *, ROW_NUMBER() over(partition by bill_no, bill_year, bill_type order by bill_no, bill_year, bill_type) as rowid from 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 and b.rowid=1 and b.BILLKEY=vb.BILLKEY
inner join dy2022.dbo.TAXDETAIL td on td.PROPERTYKEY=b.PROPERTYKEY and td.TAXTYPE=3	
where b.BALANCE_DUE > 0

--where b.bill_type IN (@proptype)
) 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 (select *, ROW_NUMBER() over(partition by bill_no, bill_year, bill_type order by bill_no, bill_year, bill_type) as rowid from 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 and b.rowid=1 and b.BILLKEY=vb.BILLKEY
inner join dy2021.dbo.TAXDETAIL td on td.PROPERTYKEY=b.PROPERTYKEY and td.TAXTYPE=3	
where b.BALANCE_DUE > 0
--where b.bill_type IN (@proptype)
) 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 (select *, ROW_NUMBER() over(partition by bill_no, bill_year, bill_type order by bill_no, bill_year, bill_type) as rowid from 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 and b.rowid=1 and b.BILLKEY=vb.BILLKEY
inner join dy2020.dbo.TAXDETAIL td on td.PROPERTYKEY=b.PROPERTYKEY and td.TAXTYPE=3	
where b.BALANCE_DUE > 0
--where b.bill_type IN (@proptype)
) 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 (select *, ROW_NUMBER() over(partition by bill_no, bill_year, bill_type order by bill_no, bill_year, bill_type) as rowid from 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 and b.rowid=1 and b.BILLKEY=vb.BILLKEY
inner join dy2019.dbo.TAXDETAIL td on td.PROPERTYKEY=b.PROPERTYKEY and td.TAXTYPE=3	
where b.BALANCE_DUE > 0
--where b.bill_type IN (@proptype)
) 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 (select *, ROW_NUMBER() over(partition by bill_no, bill_year, bill_type order by bill_no, bill_year, bill_type) as rowid from 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 and b.rowid=1 and b.BILLKEY=vb.BILLKEY
inner join dy2018.dbo.TAXDETAIL td on td.PROPERTYKEY=b.PROPERTYKEY and td.TAXTYPE=3	
where b.BALANCE_DUE > 0
--where b.bill_type IN (@proptype)
) 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 (select *, ROW_NUMBER() over(partition by bill_no, bill_year, bill_type order by bill_no, bill_year, bill_type) as rowid from 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 and b.rowid=1 and b.BILLKEY=vb.BILLKEY
inner join dy2017.dbo.TAXDETAIL td on td.PROPERTYKEY=b.PROPERTYKEY and td.TAXTYPE=3	
where b.BALANCE_DUE > 0
--where b.bill_type IN (@proptype)
) 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 (select *, ROW_NUMBER() over(partition by bill_no, bill_year, bill_type order by bill_no, bill_year, bill_type) as rowid from 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 and b.rowid=1 and b.BILLKEY=vb.BILLKEY
inner join dy2016.dbo.TAXDETAIL td on td.PROPERTYKEY=b.PROPERTYKEY and td.TAXTYPE=3	
where b.BALANCE_DUE > 0
--where b.bill_type IN (@proptype)
) 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 (select *, ROW_NUMBER() over(partition by bill_no, bill_year, bill_type order by bill_no, bill_year, bill_type) as rowid from 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 and b.rowid=1 and b.BILLKEY=vb.BILLKEY
inner join dy2015.dbo.TAXDETAIL td on td.PROPERTYKEY=b.PROPERTYKEY and td.TAXTYPE=3	
where b.BALANCE_DUE > 0
--where b.bill_type IN (@proptype)
) 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 (select *, ROW_NUMBER() over(partition by bill_no, bill_year, bill_type order by bill_no, bill_year, bill_type) as rowid from 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 and b.rowid=1 and b.BILLKEY=vb.BILLKEY
inner join dy2014.dbo.TAXDETAIL td on td.PROPERTYKEY=b.PROPERTYKEY and td.TAXTYPE=3	
where b.BALANCE_DUE > 0
--where b.bill_type IN (@proptype)
) 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 (select *, ROW_NUMBER() over(partition by bill_no, bill_year, bill_type order by bill_no, bill_year, bill_type) as rowid from 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 and b.rowid=1 and b.BILLKEY=vb.BILLKEY
inner join dy2013.dbo.TAXDETAIL td on td.PROPERTYKEY=b.PROPERTYKEY and td.TAXTYPE=3	
where b.BALANCE_DUE > 0
--where b.bill_type IN (@proptype)
) 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 (select *, ROW_NUMBER() over(partition by bill_no, bill_year, bill_type order by bill_no, bill_year, bill_type) as rowid from 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 and b.rowid=1 and b.BILLKEY=vb.BILLKEY
inner join dy2012.dbo.TAXDETAIL td on td.PROPERTYKEY=b.PROPERTYKEY and td.TAXTYPE=3	
where b.BALANCE_DUE > 0
--where b.bill_type IN (@proptype)
) q group by bill_year

) z 
group by BILL_YEAR 
order by BILL_YEAR desc