declare @curr_year as int = 2022
declare @acct_code as varchar(30) = 303

select (select sum(credit-debit)--, count(*) as CY_PROPERTY 
from dy2022.dbo.GENERAL_LEDGER g
inner join dy2022.dbo.vPAYMENT p on p.BILL_NO=g.BILL_NO and p.BILL_YEAR=g.BILL_YEAR and g.PAYMENTKEY=p.PAYMENTKEY
where ACCT_CODE=@acct_code 
	and convert(date,trans_date) between '20230301' and '20230331'
	and g.BILL_YEAR >= @curr_year
	--and isnull(g.PENALTY,0)=0 
	and isnull(g.interest,0)=0
	and p.BILL_TYPE in ('R','P')
) PROPERTY_CURRENT

, (select sum(credit-debit) as PY_PROPERTY 
from dy2022.dbo.GENERAL_LEDGER g
inner join dy2022.dbo.vPAYMENT p on p.BILL_NO=g.BILL_NO and p.BILL_YEAR=g.BILL_YEAR and g.PAYMENTKEY=p.PAYMENTKEY
where ACCT_CODE=@acct_code 
	and convert(date,trans_date) between '20230301' and '20230331'
	and g.BILL_YEAR < @curr_year
	and isnull(g.PENALTY,0)=0 
	and isnull(g.interest,0)=0
	and p.BILL_TYPE in ('R','P')
	) PROPERTY_PRIOR

, (select sum(credit-debit) as PEN_INT 
from dy2022.dbo.GENERAL_LEDGER g
inner join dy2022.dbo.vPAYMENT p on p.BILL_NO=g.BILL_NO and p.BILL_YEAR=g.BILL_YEAR and g.PAYMENTKEY=p.PAYMENTKEY
where ACCT_CODE=@acct_code 
	and convert(date,trans_date) between '20230301' and '20230331'
	and (isnull(g.PENALTY,0)=1 or isnull(g.interest,0)=1)
	and p.BILL_TYPE in ('R','P')
) PROPERTY_PEN_INT

, (select sum(credit-debit) as TIMB
from dy2022.dbo.GENERAL_LEDGER g
inner join dy2022.dbo.vPAYMENT p on p.BILL_NO=g.BILL_NO and p.BILL_YEAR=g.BILL_YEAR and g.PAYMENTKEY=p.PAYMENTKEY
where ACCT_CODE=@acct_code 
	and convert(date,trans_date) between '20230301' and '20230331'
	and isnull(g.PENALTY,0)=0 
	and isnull(g.interest,0)=0
	and p.BILL_TYPE in ('T')
) TIMBER

, (select sum(credit-debit) as TIMB_PEN_INT 
from dy2022.dbo.GENERAL_LEDGER g
inner join dy2022.dbo.vPAYMENT p on p.BILL_NO=g.BILL_NO and p.BILL_YEAR=g.BILL_YEAR and g.PAYMENTKEY=p.PAYMENTKEY
where ACCT_CODE=@acct_code 
	and convert(date,trans_date) between '20230301' and '20230331'
	and (isnull(g.PENALTY,0)=1 or isnull(g.interest,0)=1)
	and p.BILL_TYPE in ('T')
) TIMBER_PEN_INT

, (select sum(credit-debit) as MH
from dy2022.dbo.GENERAL_LEDGER g
inner join dy2022.dbo.vPAYMENT p on p.BILL_NO=g.BILL_NO and p.BILL_YEAR=g.BILL_YEAR and g.PAYMENTKEY=p.PAYMENTKEY
where ACCT_CODE=@acct_code 
	and convert(date,trans_date) between '20230301' and '20230331'
	and isnull(g.PENALTY,0)=0 
	and isnull(g.interest,0)=0
	and p.BILL_TYPE in ('M')
) MH

, (select sum(credit-debit) as MH_PEN_INT 
from dy2022.dbo.GENERAL_LEDGER g
inner join dy2022.dbo.vPAYMENT p on p.BILL_NO=g.BILL_NO and p.BILL_YEAR=g.BILL_YEAR and g.PAYMENTKEY=p.PAYMENTKEY
where ACCT_CODE=@acct_code 
	and convert(date,trans_date) between '20230301' and '20230331'
	and (isnull(g.PENALTY,0)=1 or isnull(g.interest,0)=1)
	and p.BILL_TYPE in ('M')
	) MH_PEN_INT

, (select sum(credit-debit) as HDE
from dy2022.dbo.GENERAL_LEDGER g
inner join dy2022.dbo.vPAYMENT p on p.BILL_NO=g.BILL_NO and p.BILL_YEAR=g.BILL_YEAR and g.PAYMENTKEY=p.PAYMENTKEY
where ACCT_CODE=@acct_code 
	and convert(date,trans_date) between '20230301' and '20230331'
	and isnull(g.PENALTY,0)=0 
	and isnull(g.interest,0)=0
	and p.BILL_TYPE in ('H')
) HDE

, (select sum(credit-debit) as HDE_PEN_INT 
from dy2022.dbo.GENERAL_LEDGER g
inner join dy2022.dbo.vPAYMENT p on p.BILL_NO=g.BILL_NO and p.BILL_YEAR=g.BILL_YEAR and g.PAYMENTKEY=p.PAYMENTKEY
where ACCT_CODE=@acct_code 
	and convert(date,trans_date) between '20230301' and '20230331'
	and (isnull(g.PENALTY,0)=1 or isnull(g.interest,0)=1)
	and p.BILL_TYPE in ('H')
) HDE_PEN_INT

, (select sum(credit-debit) 
from dy2022.dbo.GENERAL_LEDGER g
inner join dy2022.dbo.vPAYMENT p on p.BILL_NO=g.BILL_NO and p.BILL_YEAR=g.BILL_YEAR and g.PAYMENTKEY=p.PAYMENTKEY
where ACCT_CODE=8004
	and convert(date,trans_date) between '20230301' and '20230331'
) BREACH
