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
select inum,supervisor,category,symn,sum(belowthen2) as belowthen2,sum(between2and5) as between2and5,sum(above5) as above5,dqty from (
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
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:
When Sum(dqty) < 2 Then 'Below'
When Sum(dqty) Between 2 And 5 Then 'Between'
End As Status
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'
CASE WHEN belowthen2 < 2 THEN 'Above'
when between 2 and 5 Then 'between2and5'
end as status
SAME RESULT..THE QUERY THAT YOU GAVE I TRIED THAT EARLIER BUT WAS WITH THE RESULT
This is the solution what I did and giving correct result set
(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'
) as STATUS
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
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 );
FROM @t t
JOIN @l l
ON t.decCol BETWEEN l.Low AND l.High;
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
IIF(dqty < 2,dqty,0) As BelowThen2,
IIF(dqty Between 2 And 5,dqty,0) As Between2And5,
IIF(dqty > 5,dqty,0) As Above5,
When dqty < 2 Then 'Below 2'
When dqty Between 2 And 5 Then 'Between 2 And 5'
Else 'Above 5'
End As Status