SQL Efficiency on Date Range or Separate Tables
<p>I'm calculating historical amount from a table in years(ex. 2015-2016, 2014-2015, etc.) I would like to seek expertise if its more efficient to do it in one batch or  repeat the query multiple times filtered by the date required.</p>

<p>Thanks in advance!</p>

<p>OPTION 1:</p>

<pre><code>select 
    id,
    sum(case when year(getdate()) - year(txndate) between 5 and 6 then amt else 0 end) as amt_6_5,
    ...
    sum(case when year(getdate()) - year(txndate) between 0 and 1 then amt else 0 end) as amt_1_0,
from 
    mytable
group by 
    id
</code></pre>

<p>OPTION 2:</p>

<pre><code>select 
    id, sum(amt) as amt_6_5
from 
    mytable 
group by 
    id
where 
    year(getdate()) - year(txndate) between 5 and 6

...

select 
    id, sum(amt) as amt_1_0
from 
    mytable 
group by 
    id
where 
    year(getdate()) - year(txndate) between 0 and 1
</code></pre>