SQL Efficiency on Date Range or Separate Tables

Question

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.

Thanks in advance!

OPTION 1:

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

OPTION 2:

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

Answer 1

This may be help you,

WITH CTE
AS
(
    SELECT  id,
            (CASE   WHEN year(getdate()) - year(txndate) BETWEEN 5 AND 6 THEN 'year_5-6'
                    WHEN year(getdate()) - year(txndate) BETWEEN 4 AND 5 THEN 'year_4-5'
                    ...
                    END)    AS my_year,
            amt
    FROM    mytable
)
SELECT  id,my_year,sum(amt)
FROM    CTE
GROUP BY id,my_year

Here, inside the CTE, just assigned a proper year_tag for each records (based on your conditions), after that select a summary for the CTE grouped by that year_tag.

Answer 2

1. Unless you have resources issues I would go with the CASE version.
Although it has no impact on the results, filtering on the requested period in the WHERE clause might have a significant performance advantage.
2. Your period definition creates overlapping.

select    id
         ,sum(case when year(getdate()) - year(txndate) = 6 then amt else 0 end) as amt_6
         -- ...
         ,sum(case when year(getdate()) - year(txndate) = 0 then amt else 0 end) as amt_0
where     txndate >= dateadd(year, datediff(year,0, getDate())-6, 0)
from      mytable
group by  id