March 31, 2008 at 11:36 am
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.
March 31, 2008 at 11:45 am
This should do the whole thing in one fell swoop:
diff = abs(coalesce(ea.lev1, 0) - coalesce(b.firstline, 0))
Should allow you to eliminate the Case statement entirely.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 31, 2008 at 11:58 am
Gsquared -
This might sound like a cliche but that was a 'great' answer & it just fit the bill.
Can you tell what was wrong with my approach...
March 31, 2008 at 12:03 pm
You needed to add more Whens.
when lev1 is null then firstline
when firstline is null then lev1
That would solve it too.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 31, 2008 at 12:04 pm
In line with the previous request, lets say how do I find the percentage, between a.lev1 & b.firstline.
As it is forecasting vis-a-vis, the value can never be greater than 1. so it should find the lowest value & place it in the numerator & highest value in the denominator & then divide one by another.
Pease help !
March 31, 2008 at 12:14 pm
I decided to test it for performance:
create table #Test (
Lev1 int,
Firstline int)
insert into #test (lev1, firstline)
select t1.number, t2.number
from common.dbo.numbers t1,
common.dbo.numbers t2
where t1.number <= 100
and t2.number <= 1000
insert into #test (lev1, firstline)
select number, null
from common.dbo.numbers
set statistics io on
set statistics time on
select abs(coalesce(lev1, 0) - coalesce(firstline, 0))
from #test
select
case
when lev1 > firstline then lev1 - firstline
when firstline > lev1 then firstline - lev1
when firstline = 0 then lev1
when lev1 = 0 then firstline
when firstline is null then lev1
when lev1 is null then firstline
else 0
end
from #test
I ran each query on a little over 110k rows, five times each.
The best time for the single-line query was under 1 millisecond, average time was 15 milliseconds, worst time was 63 milliseconds.
Case statement best time was 32 milliseconds, average was 65, worst was 125.
So either one is valid, but the single-line query is definitely faster.
Both had identical IO statistics: 1 scan, 234 reads.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 31, 2008 at 12:17 pm
pommguest99 (3/31/2008)
In line with the previous request, lets say how do I find the percentage, between a.lev1 & b.firstline.As it is forecasting vis-a-vis, the value can never be greater than 1. so it should find the lowest value & place it in the numerator & highest value in the denominator & then divide one by another.
Pease help !
select min(a.lev1)/max(b.firstline)
from dbo.YourTableName
Should do it. You may need to add a "group by" clause to that. Look up aggregate functions and Group By in Books Online to see explanations for that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply