PeterBaileyUk
asked on
sql server query
I am trying not to show records where the data change went from a value to 0 but whatever i try fails as the variance is a whole number and not sure how to get around that.
0 72.4 201701 72 Mpg3 here is good the variance is 72 and previous value 0
42.2 0 201701 42 Mpg3 here went from value to zero
i can see its contradicting itself because of the where variance >10 so its showing both directions
my code to set the variance is
The query has to cope with textual change too which it appears to do so far fine but same applies any change from something to nothing would be ignored
Prev
3 Series GT 2013 On
change
3 Series GT 2013 To 2016
ok
but prev "hello"
change "" i could ignore
select pk_id, v.Prev, v.change, v.ChangeYearMonth, v.ActualVariance, fieldname
from TblCompareEvents as v
WHERE v.ActualVariance >10
or ISNULL(LTRIM(RTRIM(v.Change)),'')<>''
and v.change<>'0'
and v.change>v.Prev
0 72.4 201701 72 Mpg3 here is good the variance is 72 and previous value 0
42.2 0 201701 42 Mpg3 here went from value to zero
i can see its contradicting itself because of the where variance >10 so its showing both directions
my code to set the variance is
UPDATE dbo.TblCompareEvents
SET ActualVariance = Abs(CAST(prev AS BIGINT) - CAST(change AS BIGINT))
WHERE TRY_CONVERT(BIGINT,Prev) IS NOT NULL
AND TRY_CONVERT(BIGINT,change) IS NOT NULL
UPDATE dbo.TblCompareEvents
SET ActualVariance = Abs(CAST(prev AS DECIMAL) - CAST(change AS DECIMAL))
WHERE TRY_CONVERT(DECIMAL,Prev) IS NOT NULL
AND TRY_CONVERT(DECIMAL,change) IS NOT NULL
The query has to cope with textual change too which it appears to do so far fine but same applies any change from something to nothing would be ignored
Prev
3 Series GT 2013 On
change
3 Series GT 2013 To 2016
ok
but prev "hello"
change "" i could ignore
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
i just found one its giving results of v.actualvariance<10
141 146 2017_02 5 VehicleCountByMvris
141 146 2017_02 5 VehicleCountByMvris
ASKER
i wonder if i need to create two parts to it one to deal with the actual variance field and then another for the textual
i just found one its giving results of v.actualvariance<10That's because the OR. You're saying variance greather than 10 or Change is not empty. This case is not empty. You should review why do you need an OR operation:
select pk_id, v.Prev, v.change, v.ChangeYearMonth, v.ActualVariance, fieldname
from TblCompareEvents as v
WHERE v.ActualVariance >10
AND v.Prev<>'0'
AND v.change>v.Prev
ASKER
that was to pickup non numerical data that went from nothing to something. hmm its interesting
ASKER