Rolling time frame without temp tables

I need to pull the sum of sales over a rolling 12 month time frame but my DBA did not give me the rights to make temp tables so I can not use those like I have seen in several posts when looking online. Also most of the most I find refer to using SQL 2012. Some of the servers I have to access are SQL 2000 some are SQL 2012 and I need this work on both. Here is so some sample code:

Select Rep
,Rep_ID
,Cast(Case when Sales_Date between '2/1/2014' and '1/31/2015' then Sum(Sales) else 0 end as decimal(18,2)) as "Sales Amount"

From dbo.Sales

Solution

So you're trying to get the sales amount for each representative over the past 12 months?

SELECT Rep
    ,Rep_ID
    ,"Sales Amount" = Sum(Sales)
FROM dbo.Sales
WHERE Sales_Date
    BETWEEN DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -12, 0) 
        AND DATEADD(SECOND, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
GROUP BY Rep, Rep_ID

EDIT: Slight bugfix, the between dates are now 2014-02-01 00:00:00.000 AND 2015-01-31 23:59:59.000