sql server 2008 - Write a query that will compare its results to the same results but with another date as reference -

i have query compares final balance of month final balance of same month year before. query works fine, issue when want check against more 2 years before, query made predecessor query takes time print results, adds query per year of want see, higher year, larger query.

another predecessor created pivot table see results present information, showing 3 years before, query when want display whole information due joins , unions query becomes inefficient time-wise.

the project has been passed on me, see original(structure/backbone) query looks in order achieve results of months final balance compared last years monthly final balance, wish make more dynamic report regardless of year/month we're looking into, , not entirely hard coded or repetition of same query on , on again. i've literally hit wall since can't come idea of how make work in more dynamic way. i'm new reporting , data analysis , that's what's limiting progress.

    select t2.[segment_0]+'-'+t2.[segment_1]+'-'+t2.[segment_2] cuenta,     t2.[acctname], sum(t0.[debit]) debito, sum(t0.[credit]) credito,      sum(t0.[debit])-sum(t0.[credit]) saldo      [server].[db1].[dbo].[jdt1] t0       inner  join [server].[db1].[dbo].[ojdt] t1       on  t1.[transid] = t0.[transid]      inner join [server].[db1].[dbo].[oact] t2     on t2.[acctcode] = t0.[account]     t0.[refdate] >= '2007-12-31'  ,  t0.[refdate] <= '2016-06-30'       group t2.[segment_0]+'-'+t2.[segment_1]+'-'+t2.[segment_2],t2.[acctname] 

i'm not looking me, can point me , guide through best possible course of action achieve this.

here suggestions:

1. it isn't clear me why need [server].[db1].[dbo].[ojdt] t1. data doesn't appear in output , isn't needed join t0 t2. if can omit it, so.

   if can't omit because need exclude transactions t0 aren't in t1, use exists clause rather joining in.

2. use cte group t0 records account, , join cte t2. way t2 doesn't have join every record in t0, summarized result. don't need group composite field , account name, because if grouping in cte, won't grouped.

   here's sort of outline of like:

   ; summed (    select  account        ,   sum(credito) sumcredito        ...       [jdt1] t0      t0.[refdate] >= ...    group account ) select  (.. composite segment field ..)      ,  accountname      ,  sumcredito    summed t1    join [oact] t2        on t1.account = t2.acctcode 

3. if want dynamic dates, need parameterize , turn stored proc if isn't 1 already.

4. push formatting (which includes pivoting already-grouped data list matrix) reporting tool possible. achieving dynamic pivoting tricky in t-sql trivial in ssrs, pick 1 tool.
5. remember, can dynamically set column headers in tool: don't have change column names in data.

hope helps.