Help with case statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with case statement

Author

Topic

gavakie
Posting Yak Master

221 Posts

Posted - 2009-02-03 : 11:15:39

Im getting an error on this case statement. We are forced to use a calendar table instead of getdate() dont ask why.

Select Case when week_of_month = 1
then (
Select max(week_of_month) from db.dbo.calendar.calendar where month_of_year = month(current_date)-1
)
else week_of_month end
From db.dbo.calendar
where calendar_date = current_date

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2009-02-03 : 11:22:51

what error?
and what happens when the current date is in january? your month(current_date)-1 would be 0 surely?

Em

gavakie
Posting Yak Master

221 Posts

Posted - 2009-02-03 : 11:27:03

it tells me there is an error after the then

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2009-02-03 : 12:01:49

Apart from logical errors such as what gavakie pointed out, the syntax problem may be because of the table name db.dbo.calendar.calendar in the inner query. You may need to change it to db.dbo.calendar

gavakie
Posting Yak Master

221 Posts

Posted - 2009-02-03 : 12:10:52

I changed that and the subquery run fine but when I run the case statement it doesnt.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-03 : 12:20:00

why dont you use a variable?


DECLARE @week_of_month int

SELECT @week_of_month=max(week_of_month) 
from db.dbo.calendar.calendar 
where month_of_year = month(current_date)-1

 Select Case when week_of_month = 1 
then @week_of_month
else week_of_month end
From db.dbo.calendar
where calendar_date = current_date

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources