Need three column in case query

Question

0.00/5 (No votes)

See more:

This is my SQL server query,
In case how to use three column i have 3 condition 1.less than 2.between and 3. greater than
So how Should i use them for three columns as see in WHen i put three condition but after when bracket there is Then and else so how to use 3 columns

I need 3 Entries IN STATUS COLUMN How to do that ,With this quer only two entries will come

select inum,supervisor,category,symn,belowthen2,between2and5,above5,dqty, (CASE 
 
     WHEN (belowthen2 < 2 and   between2and5 between 2 and 5 and above5 >5 ) THEN 'Above'  and ELSE 'Below' 

 END) as STATUS

from (

select inum,supervisor,category,symn,sum(belowthen2) as belowthen2,sum(between2and5) as between2and5,sum(above5) as above5,dqty from (
select inum,supervisor,category,symn,dqty,
CASE WHEN sum(dqty)  < 2 THEN dqty
ELSE 0 end as belowthen2 ,
CASE WHEN sum(dqty)  between 2 and 5 THEN dqty
ELSE 0 end as between2and5 ,
CASE WHEN sum(dqty)  > 5 THEN dqty
ELSE 0 end as above5 
 from (
 
 ...)

What I have tried:

No idea, I have used case with only 2 conditions yet but now need 3 condition in STATUS Column

Posted 30-Jul-18 20:31pm

Member 13518187

Updated 31-Jul-18 2:38am

Add a Solution

Comments

Naga Sindhura 1-Aug-18 3:03am

syntax error in the select statement. So try this:
DECLARE @testdata TABLE (belowthen2 INT, between2and5 INT, above5 INT)
INSERT INTO @testdata(belowthen2, between2and5, above5)
SELECT 1,3,6 UNION
SELECT 3,3,6 UNION
SELECT 1,6,6

select belowthen2, between2and5, above5,CASE WHEN (belowthen2 < 2 and (between2and5 between 2 and 5) and above5 >5 ) THEN 'Above' ELSE 'Below' END as STATUS
FROm @testdata

if the issue got fixed this then thats fine otherwise you need to look at your from and where part as well

Naga Sindhura 1-Aug-18 3:06am

if you need one more entry like mentioned in Solution 4. Please refer that solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2018-07-31T01:45:00

There is a bug in your code in

SQL

WHEN (belowthen2 < 2 and   between2and5 between 2 and 5 and above5 >5 ) THEN 'Above'  and ELSE 'Below'

Remove the and that I've highlighted

There are some good examples on how to have multiple conditions in a CASE statement on the documentation site - CASE (Transact-SQL) | Microsoft Docs[^]

Here is a trivial example using this sample data:

SQL

create table #example (a int)
insert into #example (a) values (0),(25), (50), (100)

This query has 4 conditions

SQL

select CASE WHEN a < 25 THEN '1st Quartile'
			WHEN a < 50 THEN '2nd Quartile'
			WHEN a < 75 THEN '3rd Quartile'
			ELSE '4th Quartile' 
			END,a 
FROM #example

giving the results

1st Quartile	0
2nd Quartile	25
3rd Quartile	50
4th Quartile	100

Here is a slightly more complex example

SQL

create table #example2 (a int, b int, c int, d int)
insert into #example2 (a,b,c,d) values
(1,0,0,0), (2,1,0,0), (1,1,0,0), (1,0,0,1)

select CASE WHEN a = 1 and d = 0 THEN 'Condition 1'
			WHEN a = 1 and b = 1 and c = 1 and d = 1 THEN 'Condition 2'
			ELSE
				'No conditions met'
			END
from #example2

Unfortunately you haven't given us your complete query, nor any sample data or expected results so I can't help you with your specific problem

Member 13518187 · Answer 2 · 2018-07-31T02:39:00

----sp_help QWERTY

select inum,supervisor,category,symn,belowthen2,between2and5,above5,dqty,


(CASE  WHEN (belowthen2 < 2 and between2and5 =0 and above5 =0 )  THEN 'belowthen2' else 
     case  when (between2and5 between 2 and 5 and belowthen2 =0 and above5=0) Then 'between2and5' else
	  case when  ( between2and5 =0 and belowthen2 =0 and above5>5)  then   'above5'
	  end 
	   end 
	   end 
	   ) as STATUS
from (
----
)o ;

Need three column in case query

2 solutions

Solution 4

Solution 5

Add your solution here

Preview 0

Existing Members

...or Join us