SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 using CASE to set vars		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

lcsgeek
Starting Member

USA
38 Posts
Posted - 01/28/2013 :  11:25:21  Show Profile  Reply with Quote
doesn't look like the following is allowed:

SELECT CASE
	WHEN DATEPART(wk,@ffTestStartDate) BETWEEN 27 AND 47 THEN --middle of year to end of first tri
		SET @dwSeasonKey = 4 --Fall
	WHEN DATEPART(wk,@ffTestStartDate) BETWEEN 11 AND 26 THEN --end of second tri to middle of year
		SET @dwSeasonKey = 2 --Spring
	ELSE
		SET @dwSeasonKey = 1 --Winter
END

using IF is rediculously ugly. Is there something I'm missing with CASE? Thanks

James K
Flowing Fount of Yak Knowledge

1500 Posts
Posted - 01/28/2013 :  11:29:12  Show Profile  Reply with Quote
The syntax is as follows:
SELECT @dwSeasonKey = 
CASE
	WHEN DATEPART(wk,@ffTestStartDate) BETWEEN 27 AND 47 THEN --middle of year to end of first tri
		4 --Fall
	WHEN DATEPART(wk,@ffTestStartDate) BETWEEN 11 AND 26 THEN --end of second tri to middle of year
		2 --Spring
	ELSE
		1 --Winter
END
Go to Top of Page

lcsgeek
Starting Member

USA
38 Posts
Posted - 01/28/2013 :  12:37:42  Show Profile  Reply with Quote
Thanks James, much appreciated.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

1500 Posts
Posted - 01/28/2013 :  13:09:36  Show Profile  Reply with Quote
You are very welcome - glad to be of help.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47069 Posts
Posted - 01/28/2013 :  13:17:30  Show Profile  Reply with Quote
just FYI
Another SQL 2012 way to do this is by using IIF


SELECT @dwSeasonKey = IIF(DATEPART(wk,@ffTestStartDate) BETWEEN 27 AND 47,4,IIF(DATEPART(wk,@ffTestStartDate) BETWEEN 11 AND 26,2,1))


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000