Computing percentage change between tables

[novice82]

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

 

 

[artacus]

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

[novice82]

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.

 

 

[artacus]

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.