Link to home
Start Free TrialLog in
Avatar of PeterBaileyUk
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.

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

Open in new window


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

Open in new window


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
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of PeterBaileyUk
PeterBaileyUk

ASKER

looking good will look at the data but i think its there. thank you
i just found one its giving results of v.actualvariance<10

141      146      2017_02      5      VehicleCountByMvris
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<10
That'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

Open in new window

that was to pickup non numerical data that went from nothing to something. hmm its interesting