Using Case to find the difference
-
Im trying to compare two column values & store the resultant in a new column called Diff. My task is to diplay the difference by substract the biggest from the smallest & both these columns are int datatype. However the problem arises when there is no other column value that you comparing to & when this condition is encountered, we need to store the value of the either one of the column values and it only shows '0000' instead of any value.
please help
'Diff' =
case
when ea.lev1 > b.firstline Then ea.lev1-b.firstline
when ea.lev1 < b.firstline then b.firstline-ea.lev1
when b.firstline = 0 then ea.lev1
when ea.lev1 = 0 then b.firstline
else '0'
end
So when there is no value in ea.lev1 then it should show the value of b.firstline & viceversa.
-
Something like this instead?
Diff = coalesce(abs(ea.lev1-b.firstline),ea.lev1,b.firstline,0)____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
When you say no value do you mean there are Null's or that one or both are 0? It would help if you posted some example data like this:
Create Table #tbl1
(
id int identity(1,1),
lev1 int
)
Create Table #tbl2
(
id int identity(1,1),
firstline int
)
Declare @i int
While Isnull(@i, 1) <> 10
Begin
Insert Into #tbl1
(
lev1
)
Select
@i
Set @i = IsNull(@i, 0) + 1
End
Declare @count Int
While IsNull(@count, 1) <10
Begin
Insert Into #tbl2
(firstline)
Select
Case
When @count = 7 Then Null
Else @i
End
Select @count = scope_Identity()
If IsNull(@i, 1) % 3 = 0
Set @i = @i - 8
Else
Set @i = IsNull(@i, 1) + 5
End
Select
*,
Case
When IsNull(T1.lev1, T2.firstline) Is Null Then 0 -- if both are null then 0
When T1.lev1 > T2.firstline then T1.lev1 - T2.firstline
When T1.lev1 < T2.firstline then T2.firstline - T1.lev1
When NullIf(T1.lev1, 0) Is Null Then T2.firstline -- lev1 null or 0 so firstline
When NullIf(T2.firstline, 0) Is Null Then T1.lev1 -- firstline is null or 0 so lev1
Else 0 -- any other possibility
End
From
#tbl1 T1 Join
#tbl2 T2 On
T1.id = T2.id
Drop table #tbl1
Drop table #tbl2
Does this get the results you want?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
CASE statement isn't necessary, just use ABS(). if you have nulls, wrap both in ISNULL().
"Diff" = ABS( ISNULL(ea.lev1,0) - ISNULL(b.firstline,0) )
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply