• What you want to do is fairly easy if you use a calendar table. Here is an article I published on the subject last July and it has an example of returning prior periods to the current period (in this case the periods are months).

    http://www.sqlservercentral.com/articles/T-SQL/70482/

    Todd Fifield