Year Over Year Comparison in SQL

Either we are slacking, or we are both so busy lately! We had a great time at Technical Airlift, and I am gearing up for the Partner Event (GPCC) and GPUG Summit next week in lovely Tampa, FL.  In the meantime, here is a little bit I put together based on a Victoria Yudin script to pull an account balance as the back end to a dashboard to display year over year balances by GL account.  So this crosses both open and history years...

--CTE to pull current year account balances by year
With CTECurrentYear (Account, AcctDesc, Balance, GLYear) as
(select a.actnumst Account, m.actdescr AcctDesc, ISNULL((sum(g.DEBITAMT-g.CRDTAMNT)),0) Balance, g.OPENYEAR GLYear
from GL20000 g
inner join GL00105 a
on a.ACTINDX = g.ACTINDX
inner join GL00100 m
on g.ACTINDX=m.ACTINDX
where a.ACTNUMST IN ('list out the GL accounts here') group by a.actnumst, m.actdescr, g.openyear),

--CTE to pull historical year account balances by year
CTEHistoryYear (Account, AcctDesc, Balance, GLYear) as
(select a.actnumst Account, m.actdescr AcctDesc, ISNULL((sum(g.DEBITAMT-g.CRDTAMNT)),0) Balance, g.HSTYEAR GLYear
from GL30000 g
inner join GL00105 a
on a.ACTINDX = g.ACTINDX
inner join GL00100 m
on g.ACTINDX=m.ACTINDX
where a.ACTNUMST IN ('list out the GL accounts here') group by a.actnumst, m.actdescr, g.HSTYEAR)

--Select statement to union both together
Select account, acctdesc, balance, GLyear from CTECurrentYear
union all
select account, acctdesc, balance, GLyear from CTEHistoryYear
order by account, GLYear

Hope to see you in Tampa!