Need three column in case query

Transact-SQL
1

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 (

...)

2

DDL, Sample data and expected output would make it a lot easier to understand what you're trying to achieve and how we can help you.

That said, are you trying to do something like:

Select
    Case
        When Sum(dqty) < 2 Then 'Below'
        When Sum(dqty) Between 2 And 5 Then 'Between'
        Else 'Above'
    End As Status
From ….
3

I tried this, but giving wrong result i status column giving only 'Above'

(CASE WHEN belowthen2 < 2 THEN 'Above' else
case when between2and5 between 2 and 5 Then 'between2and5' else
case when above5 > 5 then 'above5'
else above5
end
end
end
) STATUS

4

CASE WHEN belowthen2 < 2 THEN 'Above'
when between 2 and 5 Then 'between2and5'
else 'above5'
end as status

5

image

SAME RESULT..THE QUERY THAT YOU GAVE I TRIED THAT EARLIER BUT WAS WITH THE RESULT

6

This is the solution what I did and giving correct result set

----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 ;

7

case
when [belowthen2] = 0 and [between2and5] = 0 then 'above5'
when [between2and5] = 0 and [above5] = 0 then 'belowthen2'
when [belowthen2] = 0 and [above5] = 0 then 'between2and5'
end as Status

8 
VALUES ( -2147483648, 2, 'Below' )
     , ( 2.00000000001, 2.99999999999, 'Between' )
     , ( 3, 2147483647, 'Above' );
INSERT @t ( decCol )
VALUES ( 0 )
     , ( -12 )
     , ( 21 )
     , ( 2.478 )
     , ( 1 )
     , ( 7 )
     , ( 2.2 )
     , ( 8 )
     , ( -24 )
     , ( 2.745 )
     , ( 14.2 );
SELECT t.decCol
     , l.Label
FROM @t t
JOIN @l l 
   ON t.decCol BETWEEN l.Low AND l.High;
9

I think you're overcomplicating it with nested queries, you don't need them.

Drop Table If Exists #Test
Create Table #Test(dqty Decimal(5,2))

Insert Into #Test
Values(3),(2),(2),(5),(2),(4),(1),(0.25),(5),(6),(3),(3)

Select 
	IIF(dqty < 2,dqty,0) As BelowThen2,
	IIF(dqty Between 2 And 5,dqty,0) As Between2And5,
	IIF(dqty > 5,dqty,0) As Above5,
	dqty,
	Case
		When dqty < 2 Then 'Below 2'
		When dqty Between 2 And 5 Then 'Between 2 And 5'
		Else 'Above 5'
	End As Status
From #Test