How to sum the total amount for the last 12 months using parameters

I want to SUM up the last 12 months values from a sql query.

The problem is to sum up the last 12 months, instead of taking just the whole year.

So when I SELECT March 2000 as default in my parameter,

I want the query to SUM up: from March 1999-March 2000.

This is what I got so far:

SELECT Name, SUM(sales) as totalsales, year_month
FROM Total_Sales
WHERE 
(year_month = @year_month)
GROUP BY Name, year_month

Thanks for any help!

--EDIT--

SELECT Name, SUM(sales) as totalsales, year_month
FROM Total_Sales
WHERE 
(year_month >= @From) AND 
(year_month <= @To)
GROUP BY Name, year_month

Added this after suggestions from "Steve Morgan"

Can we add a average for the last 12 months somehow without selecting 12 months in the parameters. Letting the "@From" parameter decide on start value. Something like: AVG(@From -12 months)??

Thanks again for the help!

--EDIT 2--

The ROW_NUMBER() function is very handy to solve this problem. My query looks like this now:

SELECT  Name, SUM(sales)
FROM    
(               
SELECT  rn = ROW_NUMBER() OVER (PARTITION BY Name ORDER BY year, year_month)
, Name
, sales = SUM(sales)
开发者_JS百科FROM     Total_Sales ts
WHERE
(year_month>= @From) AND 
(year_month<= @To)
GROUP BY
Name
, year
, year_month
    ) ts 
WHERE
rn <= 12    

GROUP BY
Name  

This code is longer than Steve Morgans, but is designed to allow the optimiser the ability to use any existing INDEX on the year and year_month fields. This is only relevant if you have a relatively large table as it is designed to efficiently know which records can be skipped.

(It also assumes MS SQL Server, but the logic is applicable to other RDBMSes.)

DECLARE
  @DateParam AS DATETIME
SELECT
  @DateParam  = '2011 June 01'

;WITH MyTable (Name, Year, Year_Month, Sales) AS
(
             SELECT 'Rod', 2010, 1, 10
   UNION ALL SELECT 'Rod', 2010, 2, 10
   UNION ALL SELECT 'Rod', 2010, 3, 10
   UNION ALL SELECT 'Rod', 2010, 4, 10 UNION ALL SELECT 'Jane', 2010, 4, 10
   UNION ALL SELECT 'Rod', 2010, 5, 10 UNION ALL SELECT 'Jane', 2010, 5, 10
   UNION ALL SELECT 'Rod', 2010, 6, 10 UNION ALL SELECT 'Jane', 2010, 6, 10
------------------------------------------------------------------------------
   UNION ALL SELECT 'Rod', 2010, 7, 10 UNION ALL SELECT 'Jane', 2010, 7, 10
   UNION ALL SELECT 'Rod', 2010, 8, 10 UNION ALL SELECT 'Jane', 2010, 8, 10
   UNION ALL SELECT 'Rod', 2010, 9, 10 UNION ALL SELECT 'Jane', 2010, 9, 10
   UNION ALL SELECT 'Rod', 2010,10, 10 UNION ALL SELECT 'Jane', 2010,10, 10
                                       UNION ALL SELECT 'Jane', 2010,11, 10
                                       UNION ALL SELECT 'Jane', 2010,12, 10
                                       UNION ALL SELECT 'Jane', 2011, 1, 10
                                       UNION ALL SELECT 'Jane', 2011, 2, 10
   UNION ALL SELECT 'Rod', 2011, 3, 10 UNION ALL SELECT 'Jane', 2011, 3, 10
   UNION ALL SELECT 'Rod', 2011, 4, 10 UNION ALL SELECT 'Jane', 2011, 4, 10
   UNION ALL SELECT 'Rod', 2011, 5, 10
   UNION ALL SELECT 'Rod', 2011, 6, 10
------------------------------------------------------------------------------
   UNION ALL SELECT 'Rod', 2011, 7, 10
)

SELECT
  Name,
  Year,
  Year_Month,
  SUM(sales) AS Total_Sales
FROM
  MyTable
WHERE
   (Year = DATEPART(YEAR, @DateParam)     AND Year_Month <= DATEPART(MONTH, @DateParam))
OR (Year = DATEPART(YEAR, @DateParam) - 1 AND Year_Month >  DATEPART(MONTH, @DateParam))
GROUP BY
  Name,
  Year,
  Year_Month

Notes:
1. This will give the results for the 12 months up to and including June 2011
2. No data prior to July 2010 will be included
3. The gap for Rod's data won't cause Note 3 to be breached
4. Jane's missing data in May 2011 and June 2011 won't cause Note 3 to be breached
5. The formulation of the WHERE clause will allow INDEXes to be used

I can't tell from your example how year_month is stored, which makes it difficult to decide if it lends itself to being queried this way.

In such cases, I use what I call a relative month, which I calculate as

(year - 2000) * 12 + (month -1)

So, January 2000 is Relative Month 0, February 2010 is Relative Month 121, etc.

This is easily convertible to/from an Year and Month using SQL Server Scalar Functions and easy to query using >, <, etc.

Edit:

Just seen your comments about what year_month is and also that you have a year. A scalar function to calculate a relative month value from your year and year_month columns will work for you.

Then you can do:

where
    dbo.GetRelativeMonth(year, year_month) >= @startRelativeMonth
    and dbo.GetRelativeMonth(year, year_month) < @endRelativeMonth

Though it's more efficient to add a relative_month column to your table and calculate the value when you write the record.

Edit 2:

You asked how GetRelativeMonth works, so here it is:

CREATE FUNCTION [dbo].[GetRelativeMonth] 
(
@Year int,
@Month int
)
RETURNS int
AS
BEGIN
DECLARE @Result int
SELECT @Result = (@Year - 2000) * 12 + (@Month - 1)
RETURN @Result
END

Assuming you are using SQL Server, you could use the ROW_NUMBER function to concoct a subset of your data from wich you can select the final result.

SQL Statement

SELECT  Name
        , SUM(sales)
FROM    (               
            SELECT  rn = ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Year, Year_Month)
                    , Name
                    , sales = SUM(sales)
            FROM    @Total_Sales ts
            WHERE   (YEAR(@Param) <= ts.Year AND MONTH(@Param) <= ts.Year_Month)
                    OR (YEAR(@Param) < ts.Year)
            GROUP BY
                    Name
                    , Year
                    , Year_Month
        ) ts 
WHERE   rn <= 12        
GROUP BY
        Name                                

Test Data

DECLARE @Param DATE = '05-01-2011'
DECLARE @Total_Sales TABLE (
    Name VARCHAR(32)
    , Sales INTEGER
    , Year_Month INTEGER
    , Year INTEGER
)

INSERT INTO @Total_Sales VALUES 
    ('Malcolm X', 1, 1, 2011)
    , ('Malcolm X', 1, 2, 2011)
    , ('Malcolm X', 1, 3, 2011)
    , ('Malcolm X', 1, 4, 2011)
    , ('Malcolm X', 1, 5, 2011)
    , ('Malcolm X', 1, 6, 2011)
    , ('Malcolm X', 1, 7, 2011)
    , ('Malcolm X', 1, 8, 2011)
    , ('Malcolm X', 1, 9, 2011)
    , ('Malcolm X', 1, 10, 2011)
    , ('Malcolm X', 1, 11, 2011)
    , ('Malcolm X', 1, 12, 2011)
    , ('Malcolm X', 1, 1, 2012)
    , ('Ben Hur', 1, 2, 2012)

Perhaps you could use the BETWEEN function? where YourDate BETWEEN minValue AND maxvalue

SELECT Name, SUM(sales) as totalsales, year_month 
FROM Total_Sales 
WHERE year_month = DATEADD(MONTH,-12,'31-March-2000') 
GROUP BY Name, year_month 

Try something like that.