TSQL SQL Server 2008

(select case ump.SIZE_OF_FIRM
 when (ump.SIZE_OF_FIRM >= 2 and ump.SIZE_OF_FIRM <= 5) then 4
 when (ump.SIZE_OF_FIRM between 6 and 10) then 3
 when (ump.SIZE_OF_FIRM between 11 and 25) then 2
 when (ump.SIZE_OF_FIRM > 25) then 1
end) as GroupTot,

The > throws an error
The "between" throws an error

How to set a value based on a range of a variable or constant
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
afaik since you're using multiple expressions, you'll have to lose the first ump.SIZE_OF_FIRM and declare the column within each WHEN line.
select case 
   when ump.SIZE_OF_FIRM >= 2 and ump.SIZE_OF_FIRM <= 5 then 4
   when ump.SIZE_OF_FIRM between 6 and 10 then 3
   when ump.SIZE_OF_FIRM between 11 and 25 then 2
   when ump.SIZE_OF_FIRM > 25 then 1
end as GroupTot,

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeff_KingstonAuthor Commented:

Saved my Friday from becoming Saturday
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
That's what were here for.  Good luck with your project.  -Jimbo
Jeff_KingstonAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for Jeff_Kingston's comment #a39528564

for the following reason:

How many times do I have to do this?
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Assuming that my first comment answered your question, you need to click on 'Accept as solution' on that comment, which will end the question.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.