How do I do a running year for a calendar report?

Question

Here is the Query I am using:

SELECT * FROM ra_ProjectCalendar 
WHERE MonthNumber between @Month and @Month + 12 and FullYear = @Year

It works great for this year, but, stops at December of this year. How do I get it to show a running year?

Answer 1

Something like this might work better - but its not clear what your intent is:

select * 
 from ra_ProjectCalendar 
 where DATEDIFF ( month , @startdate , @enddate ) <=12

Edit: This is SQl server syntax

Answer 2

Actually pass in and store the date rather than month and year, and compare with the current date using DateDiff:

WHERE DATEDIFF(m, @PassedDate, StoredDateColumn) < 12

Note that I used 12 months, rather than 1 year, because DATEDIFF counts the number of boundries crossed. So in december, using datediff with the year datepart would return one after only one month. You really want a 12 month span.

Answer 3

how is your date stored?

from your sql, i guess that you have one field for month, one for year and one for day. i'd recommend having one single datetime field instead, and then you can use the DateAdd() method to add 12 months (or any other interval).

EDIT: it was pointed out to me in a comment that one thing you gain on this is performance - which is more or less important depending on the scale of your applicaiton, but always nice to be aware of. if you run this query in a stored procedure, here's what you'll do:

DECLARE @oneYearAhead datetime;
SET @oneYearAhead = DateAdd(m, 12, @PassedDate)

SELECT * FROM ra_ProjectCalendar
 WHERE @PassedDate <= [Date] AND [Date] <= @oneYearAhead;

with the above code, you will get all entries between the date you pass, and one year ahead of it. (i am not 100% sure on the syntax to declare and set the @oneYearAhead variable, but you get the idea...). Note that the [] that i use around the column name Date allows me to use reserved words in column names - i have made it a habit, instead of memorizing which words are reserved...

Answer 4

Try this:

SELECT * FROM ra_ProjectCalendar 
WHERE 
  (MonthNumber > @Month AND FullYear = @Year)
  OR (MonthNumber < @Month AND FullYear = @Year + 1)

Answer 5

Depending on how your information is stored (it's a little difficult to tell from your query), either DATEADD or DATEDIFF will probably do you right. There's a list of the availabe date functions on MSDN.