T-SQL: Compute Subtotals For A Range Of Rows

Question

MSSQL 2008. I am trying to construct a SQL statement which returns the total of column B for all rows where column A is between 2 known ranges. The range is a sliding window, and should be recomputed as it might be using a loop.

Here is an example of what I'm trying to do, much simplified from my actual problem. Suppose I have this data:

table: Test

Year        Sales
----------- -----------
2000        200
2001        200
2002        200
2003        200
2004        200
2005        200
2006        200
2007        200
2008        200
2009        200
2010        200
2011        200
2012        200
2013        200
2014        200
2015        200
2016        200
2017        200
2018        200
2019        200

I want to construct a query which returns 1 row for every decade in the above table, like this:

Desired Results:

DecadeEnd  TotalSales 
---------  ----------
2009        2000
2010        2000    

Where the first row is all the sales for the years 2000-2009, the second for years 2010-2019. The DecadeEnd is a sliding window that moves forward by a set ammount for each row in the result set. To illustrate, here is one way I can accomplish this using a loop:

declare @startYear int
set @startYear = (select top(1) [Year] from Test order by [Year] asc)
declare @endYear int
set @endYear = (select top(1) [Year] from Test order by [Year] desc)

select @startYear, @endYear

create table DecadeSummary (DecadeEnd int, TtlSales int)
declare @i int
-- first decade ends 9 years after the first data point
set @i = (@startYear + 9)   
while @i <= @endYear
begin
    declare @ttlSalesThisDecade int
    set @ttlSalesThisDecade = (select SUM(Sales) from Test where(Year <= @i and Year >= (@i-9)))
    insert into DecadeSummary values(@i, @ttlSalesThisDecade)

    set @i = (@i + 9)
end

select * from DecadeSummary

This returns the data I want:

DecadeEnd   TtlSales
----------- -----------
2009        2000
2018        2000

But it is very inefficient. How can I construct such a query?

Answer 1

IMHO for complex operations you should use .NET method from assembly registered in SQL server. Since SQL 2005 you can register .NET assembly and call its method from SQL server.

Managed code in SQL

Answer 2

How about something like

SELECT  (Year / 10) * 10,
        SUM(Sales)
FROM    @Table
GROUP BY (Year / 10) * 10

Have a look at the example here

DECLARE @Table TABLE(
    Year INT,
    Sales FLOAT
)

INSERT INTO @Table SELECT 2000,200 
INSERT INTO @Table SELECT 2001,200 
INSERT INTO @Table SELECT 2002,200 
INSERT INTO @Table SELECT 2003,200 
INSERT INTO @Table SELECT 2004,200 
INSERT INTO @Table SELECT 2005,200 
INSERT INTO @Table SELECT 2006,200 
INSERT INTO @Table SELECT 2007,200 
INSERT INTO @Table SELECT 2008,200 
INSERT INTO @Table SELECT 2009,200 
INSERT INTO @Table SELECT 2010,200 
INSERT INTO @Table SELECT 2011,200 
INSERT INTO @Table SELECT 2012,200 
INSERT INTO @Table SELECT 2013,200 
INSERT INTO @Table SELECT 2014,200 
INSERT INTO @Table SELECT 2015,200 
INSERT INTO @Table SELECT 2016,200 
INSERT INTO @Table SELECT 2017,200 
INSERT INTO @Table SELECT 2018,200 
INSERT INTO @Table SELECT 2019,200 

SELECT  (Year / 10) * 10,
        SUM(Sales)
FROM    @Table
GROUP BY (Year / 10) * 10

OUTPUT

Decade      SumOfSales
----------- ----------------------
2000        2000
2010        2000

Answer 3

How about:

select sum(sales) as TotalSales, max([year]) as DecadeEnd from Test
group by year / 10

You don't have to do (year / 10) * 10 as long as Year is an integer.

Edit: If year is a float, and the interval is 2.5 years rather than 10

select sum(sales) as TotalSales, max([year]) as DecadeEnd from Test
group by convert(integer, (year * 10)) / 25