0

Current I'm using:

FLOOR(cash / 100) * 100

Issue what I have is to match the values like 

CASH     BANK
699.54   702.45

As when I run the formula it is equal to:

CASH     BANK
600      700

I have NO IDEA HOW but, is it anyway of join in this circumstances?

  • In general, it is a really bad idea to be matching on float values and usually on numeric values. You should provide sample data -- as tables -- and expected results and explain why you are rounding the value in some cases but not all. – Gordon Linoff Oct 29 '18 at 11:51
  • This is a section from my previous question - https://stackoverflow.com/questions/53007256/sql-match-shop-cash-with-bank-cash, in this case, they only have this information and I need to try to join based on CASH from bank and store, so currently looking for the best way to do it. I have provided a small sample, I more need a help with logic rather than with the actual answer. if it even possible. –  Oct 29 '18 at 11:55
  • @a_horse_with_no_name Google BigQuery –  Oct 29 '18 at 11:55

1 Answers1

0

Instead of using FLOOR you could use:

ROUND(cash/100,0)*100

Or if you had an expected margin of error you could join on something like:

BANK between CASH-5 AND CASH+5

Really it all depends on how confident you are that these will not cause errors to creep into your data or how much error you can accept before the errors override the benefits you are getting.

Bobbylank
  • 1,906
  • 7
  • 15
  • Let's say this is the correct answer, based on research, there is not yet a tool to join based on the smallest difference. –  Oct 29 '18 at 16:37
  • The problem is that a small difference to you could be massive to someone else – Bobbylank Oct 29 '18 at 16:58
  • @Babbylank currently I'm not looking for 100% match.... but at least it will help to match around 80%... –  Oct 29 '18 at 20:35