Hi gurus what is the best way to compare 2 numeric columns using SQL and show the match percentage. I have my premium values coming from 2 different sources and have to determine how much percentage they are matching. I was able to get the new_premium appended to my older dataset and get it in the below format.
with t as
( select '1692478' as ACCOUNT_ID, to_Date('12/12/2018','mm/dd/yyyy') as PROCESS_DT, 'WC' as LOB, 200 as OLD_PREM, 200 as NEW_PREM
from dual Union all
select '1692478' as ACCOUNT_ID, to_Date('12/12/2018','mm/dd/yyyy') as PROCESS_DT, 'BOP' as LOB, 300 as OLD_PREM, 305 as NEW_PREM
from dual Union all
select '1692478' as ACCOUNT_ID, to_Date('12/12/2018','mm/dd/yyyy') as PROCESS_DT, 'WC' as LOB, 980 as OLD_PREM,986 as NEW_PREM
from dual Union all
select '168890' as ACCOUNT_ID, to_Date('12/12/2017','mm/dd/yyyy') as PROCESS_DT, 'AU' as LOB, 800 as OLD_PREM,830 as NEW_PREM
from dual Union all
select '168890' as ACCOUNT_ID, to_Date('12/12/2017','mm/dd/yyyy') as PROCESS_DT, 'BOP' as LOB, 1000 as OLD_PREM,1003 as NEW_PREM
from dual
Union all
select '168890' as ACCOUNT_ID, to_Date('12/12/2017','mm/dd/yyyy') as PROCESS_DT, 'BOP' as LOB, 1000 as OLD_PREM,1000 as NEW_PREM
from dual
)
select ACCOUNT_ID, PROCESS_DT, LOB, old_prem, new_prem from t order by 1
My goal is to check match percentages for each Account_ID first, but also look to see if any patterns i.e match percentage for LOB, Year etc.
Initial thought is to do something like below and do a case logic to see if DIFF = 0 then 'MATCH' and if DIFF between 0 and 5 then 'WITHIN 5%' etc....but wondering if there is any other cool way of doing this.
select ACCOUNT_ID, PROCESS_DT, LOB, old_prem, new_prem, (old_prem - new_prem) DIFF from t order by 1
Any thoughts are appreciated.
Thanks,
Kon