Current Month & Year query?

I am writing a usage query, I need to determine what the current month and year is. If the current month is 1, then I need Year-1. I keep getting this error Line 5: Incorrect syntax near '='.

If I comment out the case statement just evaluate the (YEAR(User_Date) = YEAR(DATEADD(yy, - 1, GETDATE())))
or (YEAR(User_Date) = YEAR(GETDATE())) by itself, the query works. What I am missing? Anybody?

1 SELECT CONVERT(char(10), User_Date, 101)AS userdate,COUNT(*)AS CNT,User_Name
2 FROM Users
3 WHERE (User_Name ='Joe Bob')AND (MONTH(User_Date) =MONTH(DATEADD(mm, - 1,GETDATE())))AND
4 case MONTH(User_Date)when 1then
5 (YEAR(User_Date) =YEAR(DATEADD(yy, - 1,GETDATE())))
6 else (YEAR(User_Date) =YEAR(GETDATE()))
7 END
8 GROUP BYCONVERT(char(10), User_Date, 101),User_Name
9 ORDER BYCONVERT(char(10), User_Date, 101),User_Name

Try this:

ANDYEAR(User_Date)=CASEMONTH(User_Date) WHEN 1 THEN YEAR(DATEADD(yy,- 1,GETDATE())) ELSE(YEAR(GETDATE())) END

|||

stsong,

You have your case statement in the where clause but it is meant to be in the select clause.

http://msdn2.microsoft.com/en-us/library/ms181765.aspx

Move the case statement to your select clause and use variables.

SELECTCONVERT(char(10), User_Date, 101)AS userdate,COUNT(*)AS CNT,User_Name,'User_Year' =caseMONTH(User_Date)when 1then YEAR(DATEADD(yy, - 1,GETDATE())))else YEAR(GETDATE()))FROM UsersWHERE (User_Name ='Joe Bob')AND (MONTH(User_Date) =MONTH(DATEADD(mm, - 1,GETDATE())))ANDENDGROUP BYCONVERT(char(10), User_Date, 101),User_NameORDER BYCONVERT(char(10), User_Date, 101),User_Name

Try that.

|||

do this in your second conditional instead and see if this works for you

YEAR('2007-01-01') = case MONTH('2007-01-01') when 1 then YEAR(DATEADD(yy, - 1, GETDATE())) else YEAR(GETDATE()) END

|||

Hi,

Syntax of you case statement is completely wrong I think in your where statement try this

WHERE (User_Name ='Joe Bob')

AND

(MONTH(User_Date) =MONTH(DATEADD(mm, - 1,GETDATE())))

AND

YEAR(User_Date) =case

when MONTH(User_Date) = 1then

YEAR(DATEADD(yy, - 1,GETDATE()))

else

YEAR(GETDATE())

END

I hope what it will help you a little.

I would recomend to do select from select in this case which will speed up your query a lot saving time on converting user date 3 times (output,group and order in order you can use new field name and it should work)

Thanks

JPazgier

|||

Hi jpazgier,

Yes you are right, my case statement is wrong, I changed the "when MONTH(User_Date) = 12then" instead of = 1 since the where statement is getting last month's data. Thank you everyone for your help!