Jump to content

Computing percentage change between tables


Recommended Posts

Hi,

 

I'm a novice to sql scripting. Am trying to figure out a design

problem, involving some arithmatic computation.

 

 

I have two tables temp1 and temp2 with two feilds

account no., ( Common in both tables )

balance ( float data type ).

 

 

1. I want to compare the balance feilds in temp1 and temp2.

print out the no. of accounts and percentage of match and mismatch.

2. output the a/c nos. whose balances dont match into a seperate

table..

 

 

another question, that I have is, how does sql handle the computation,

if a value in a particular field is divided by 0 ?

 

 

any help, will get me started.

 

 

thanks

 

 

Link to comment
Share on other sites

You should do a little research on joins. You didn't give us enough info. Is there an exact 1:1 relationship between the accounts in the two tables or are their missing entries from one or both tables? And percentage of what, the percentage of temp1.balance/temp2.balance will be different than temp2.balance/temp1.balance.

 

Also at the schema level, you should use a precise data type (numeric) for the balance instead of float.

 

This makes some assumptions that may be incorrect but it should get you close:

 

SELECT temp1.account_no, temp1.balance, 
CASE WHEN temp1.balance = temp2.balance THEN 0.0
ELSE ABS(temp1.balance = temp2.balance) / GREATEST(temp1.balance, temp2.balance)
END
FROM temp1
JOIN temp2 ON temp1.account_no = temp2.account_no

Link to comment
Share on other sites

Hi,

 

Thanks a lot for the code.. its very neatly gets rid of my zero divisor problem. I have one question though: Like you pointed out there is no 1:1 relationship. I should've mentioned it earlier in my post.

 

For all account numbers in temp2 ( which has lesser records than temp1 ( the master record )), i want to calculate the balance differences in temp2 and temp1 and output to a new table.

 

 

Link to comment
Share on other sites

I'd recommend against outputting to another table. Create a view instead. (Unless you are talking hundreds of millions of accounts, in which case you'd probably be making 6 figures and answering my questions)

 

It sounds like all records in temp2 would have a match in temp1 so the query will be fine. Otherwise, you'd have to use a left outer join. You may also need to account for an edge case where perhaps they sent in too much money and their balance is negative in one table and 0 in another. That would still cause a div by 0 error.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.