[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 218
  • Last Modified:

Compare invoice values from 2 tables with a 5p tolerance


We are converting from our old system to a new system (rental application) - we are importing contracts from the old to the new - I have to run parallel Invoice runs on both then extract this invoice data from both systems and do a comparison

Due to differences in rounding and VAT calculations I need to compare using a 5p tolerance e.g.

£96.60 - old data
£96.63 - new data

This will be ignored in my query but:

£96.60 - old data
£96.66 - new data

This will flag up a problem in my query

I have a one - to - one exact matching query which I need to modify to cope with this tolerance:

SELECT [OldData].[Trans_date], [OldData].[Ax_acc], [OldData].[Cust_ref], [OldData].[Depot], [OldData].[Cont], [OldData].[Ax_code], [OldData].[SumOfinvl_qty], [OldData].[SumOfAmt], "OldInvoiceData" AS DSet

FROM [OldData] LEFT JOIN  [NewData] ON [OldData].[Cont] = [NewData].[Contract] and [OldData].[Ax_code] = [NewData].[Item] and [OldData].[Trans_Date] = [NewData].[InvDate]

WHERE ((([OldData].[SumOfinvl_qty])<>[NewData].[SumOfQty]))
   OR ((([OldData].[SumOfAmt])<>[NewData].[SumOfLineAmt])) ----------Line to be modified for tolerance
   OR ((([NewData].[Contract]) Is Null))

UNION ALL SELECT [NewData].[InvDate], [NewData].[CustAcct], [NewData].[CustOrder], [NewData].[Depot], [NewData].[Contract], [NewData].[Item], [NewData].[SumOfQty], [NewData].[SumOfLineAmt], "NewInvoiceData" AS DSet

FROM [NewData] LEFT JOIN [OldData] ON [NewData].[Contract] = [OldData].[Cont] and [NewData].[Item] = [OldData].[Ax_code] and [NewData].[InvDate] = [OldData].[Trans_date]

WHERE ((([NewData].[SumOfQty])<>[OldData].[SumOfinvl_qty]))
   OR ((([NewData].[SumOfLineAmt])<>[OldData].[SumOfAmt])) ----------Line to be modified for tolerance
   OR ((([OldData].[Cont]) Is Null))

ORDER BY [OldData].[Cont], DSet;

This Exact match query works perfectly well on like for like data (I manually modified the data to generate some errors to prove this)

The Lines I need to be  modified are highlighted above

I have tried using:

   OR round((([OldData].[SumOfAmt]+0.05)/100)*100)<>round((([NewData].[SumOfLineAmt]+0.05)/100)*100)
   OR round((([NewData].[SumOfLineAmt]+0.05)/100)*100)<>round((([OldData].[SumOfAmt]+0.05)/100)*100)

but this doesn't give me anything (this was suggested by somone to try)

Please help as this is now very urgent

If there is any more Info please ask

Thanks in advance

  • 3
  • 2
1 Solution
try this:

OR (Abs([NewData].[SumOfLineAmt] - [OldData].[SumOfAmt]  <= .05 * [OldData].[SumOfAmt])
I think, if i read this right, that this will get you what you want:

WHERE ([NewData].[SumOfQty]<>[OldData].[SumOfinvl_qty])
   OR (
          ([NewData].[SumOfLineAmt] - [OldData].[SumOfAmt]) > 5
     OR ([OldData].[SumOfAmt] - [NewData].[SumOfLineAmt]) > 5
   OR ([OldData].[Cont] Is Null)
Without the extraneous left parenthesis:

OR (Abs[NewData].[SumOfLineAmt] - [OldData].[SumOfAmt]  <= .05 * [OldData].[SumOfAmt])
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Abs for absolute value!  Kudos mbizup I had forgotten that one.
I was reading 5p as 5%.  Rererading this, 5p is pence to UK folks, right?  So if a 5 pence tolerance is needed (not 5% tolerance) the ABS function should look like this (and instead of removing a left parenthesis, I should have added a right one. :-(  )

OR (Abs([NewData].[SumOfLineAmt] - [OldData].[SumOfAmt])  <= .05)
SpeedyDirectAuthor Commented:
Many Thanks to both of you for your responses but:

with a slight modification to mbizup's last comment i.e.

>= 0.06 --------------- not less than

This gives me exactly what I want which is to find all invoices with more than a 5 "pence" difference (these are problems)

So all points to mbizup

Many thanks again


Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now