CODEWITHSUNDEEP
sqlsql-serverexcept
SouravA
SouravA

Reputation: 5243

EXCEPT returning unpredictable results

I have a table DB_Budget which has 3 columns Business_Unit_Code, Ledger_Period, Budget_GBP. For sake of simplicity, I have left out the other columns.

Data types are -

enter image description here

This table is present in my development and production environment.

While doing some quality checks I ran the below query:

select 
Business_Unit_Code,
Ledger_Period,
Budget_GBP
from [SomeLinkedServer].[Database].dbo.DB_BUDGET
where business_unit_code = 'AV' and ledger_period = '200808'
and budget_gbp >= 32269

except

select 
Business_Unit_Code,
Ledger_Period, 
Budget_GBP
from [Database].dbo.DB_BUDGET
where business_unit_code = 'AV' and ledger_period = '200808'
and budget_gbp >= 32269

I got this -

enter image description here

If I remove the except, this is what I get -

enter image description here

Clearly, data is same in both tables! Why would EXCEPT give me one row?

Things get interesting. I wrap Budget_GBP around LTRIM(RTRIM( ... construct. And things matched!

I did a bit of googling and found that LTRIM(RTRIM( basically rounds off the float to 32269.2. That might be the reason why they match.

So, to summarize, my first question is why the EXCEPT gives a row in result when the records are matching?

My second question might be simple. As you can see, I am restricted to use the clause budget_gbp >= 32269 in WHERE clause. Reason is when I provide the exact value(which I am copying from SSMS), I get no results. Please let me know what I am doing wrong here.

EDIT - Is there any way the data validation might work? There are 100s of table in the database and it is next to impossible for me to scavenge for float columsn and wrap them around cast. Using EXCEPT is one ways of validating the data in development environment.

Upvotes: 1

Views: 1797

Answers (2)

A ツ
A ツ

Reputation: 1267

you could define an accuracy and check if the value is within the specified accuracy of each other.

declare @accuracy  float = 0.001

select * 
  from      DB_BUDGET1 t1
  full join DB_BUDGET2 t2 
    on  t1.Business_Unit_Code=t2.Business_Unit_Code
    and t1.Ledger_Period     =t2.Ledger_Period
    and t1.Budget_GBP between t2.Budget_GBP-@accuracy  and t2.Budget_GBP+@accuracy 
  where t1.id is null
     or t2.id is null

http://www.sqlfiddle.com/#!3/41da0/2/0

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Cast the values to fixed point types or strings and re-run your code. The first query would look like:

select Business_Unit_Code, Ledger_Period, cast(Budget_GBP as decimal(18, 2)) as Budget_GBP

With numbers, what you see is not always what you get. So you see 32669.19, but it might really be 32.669.189999.

Upvotes: 2

Related Questions