SQLTeam.com | Weblogs | Forums

Calculating 12 X the monthly rate


#1

Hello everyone,
I have been asked to modify my query to identify accounts that have a credit balance > $500, but also where the balance is 12 times the monthly service rate. I've limited the amount of records coming back by simply adding a where clause > $500

where curr_balance < -500

However, I'm a little unsure as to how I would add the parameter to identify accounts in which the balance is 12 times the monthly rate. Would I be able to use a simple multiplication calculation? Such as:

where curr_balance < -500 and curr_balance > (12 * monthly_rate)

Also, if I am able to use the above formula, how do I write it so the code understands what I'm asking when the monthly rate is a positive number, however, the current balance number is negative? I.e. the current balance is $(1,200), but the monthly rate is $150. Any help is greatly appreciated! Thank you!

Damian


#2

Ugh! double post. See below.


#3

Do you want to get the rows where the balance is EXACTLY equal to 12 times the monthly service rate? That is what your description says. I am assuming that it is less than or greater than rather than exactly equal to.

For example, if your monthly service rate is 150, then 12 month would 1800. If you want to get that row,
where curr_balance < -500 and curr_balance < -(12*monthlyrate)

That will return a row only if both conditions are satisfied. Perhaps you need an OR condition?

Manually compute a few examples and figure out which ones you want to keep and which ones you want to eliminate. Then, it would be clear whether you want to use AND or OR.


#4

Hi JamesK,
Thank you for your response. I thought I had the right idea, but it didn't occur to me to add the negative sign before the calculation. Once I saw your response I realized I had the wrong sign within the calculation. Oh, and I need to use AND, because I'm only interested in accounts that have a balance > $500 AND where the balance is 12 times the monthly rate. Thanks again for your help!

Damian