SpeedyDirect
asked on
Compare invoice values from 2 tables with a 5p tolerance
Experts
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_qt y])<>[NewD ata].[SumO fQty]))
OR ((([OldData].[SumOfAmt])<> [NewData]. [SumOfLine Amt])) ----------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 ])<>[OldDa ta].[SumOf Amt])) ----------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].[SumOfAm t]+0.05)/1 00)*100)<> round((([N ewData].[S umOfLineAm t]+0.05)/1 00)*100)
and
OR round((([NewData].[SumOfLi neAmt]+0.0 5)/100)*10 0)<>round( (([OldData ].[SumOfAm t]+0.05)/1 00)*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
Dave
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],
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_qt
OR ((([OldData].[SumOfAmt])<>
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])<>
OR ((([NewData].[SumOfLineAmt
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].[SumOfAm
and
OR round((([NewData].[SumOfLi
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
Dave
I think, if i read this right, that this will get you what you want:
WHERE ([NewData].[SumOfQty]<>[Ol dData].[Su mOfinvl_qt y])
OR (
([NewData].[SumOfLineAmt] - [OldData].[SumOfAmt]) > 5
OR ([OldData].[SumOfAmt] - [NewData].[SumOfLineAmt]) > 5
)
OR ([OldData].[Cont] Is Null)
WHERE ([NewData].[SumOfQty]<>[Ol
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].[SumOfLineAm t] - [OldData].[SumOfAmt] <= .05 * [OldData].[SumOfAmt])
OR (Abs[NewData].[SumOfLineAm
Abs for absolute value! Kudos mbizup I had forgotten that one.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
OR (Abs([NewData].[SumOfLineA