Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

I am trying to get an avearge balance with in my sql select stmt but not having any luck, it works in Excel but when i apply same logic in sql it does not, it gives me wrong average balance....

select stmt

case when l.apr = 0 then 0 else abs(l.draccr/(l.apr/365*(365/12))) end as AAVGBALA,

here is an excel formula, which works

=+B4/(B3/365*(365/12))

B4 = l.draccr

B3 = l.apr

any ideas please

select stmt

case when l.apr = 0 then 0 else abs(l.draccr/(l.apr/365*(3

here is an excel formula, which works

=+B4/(B3/365*(365/12))

B4 = l.draccr

B3 = l.apr

any ideas please

Not the abs() is not part of Excel formula. The + is not a absolute value, but I will leave in case you do want that.

Try this:

```
case when l.apr = 0 then 0
else abs((l.draccr * 1.0)/((l.apr * 1.0)/365.0 * (365.0/12)))
end as AAVGBALA
```

B4 = l.draccr = ??

B3 = l.apr = ??

If you were doing the math manually, what would you do:

e.g. l.apr/365 then muliply result by 365/12 then divide l.draccr by that result

How do you know that the Excel value is accurate basically -- how would you double check value?

Please provide that numbers you plug into those two columns/fields in Excel and I will take a look.

=+B4/(B3/365*(365/12))

The reason I ask, is that it doesn't make a whole lot of sense.

First, it's equivalent to =+B4/(B3/12)

Second, the expression after the first division sign appears to be like a avg monthly percentage rate. Doesn't seem you yeild anything meaningful as a denominator: i.e. what is x% per month per B4?

B4 = l.draccr = 23.65

B3 = l.apr = 6.90 %

dqmq, i have attached the excel sheet with the formula, please see

Copy-of-Ave-balance-formula.xls

The SQL equivalent is the following:

l.draccr = 23.65

l.apr = 0.069

SELECT CASE l.apr WHEN 0 THEN 0 ELSE l.draccr/(l.apr/12) END AS AAVGBALA

```
DECLARE @draccr MONEY, @apr MONEY
SET @draccr = 23.65
SET @apr = 0.069
SELECT CASE @apr WHEN 0 THEN 0 ELSE @draccr/(@apr/12) END AS AAVGBALA
```

Anyway, hopefully this is helping.

this one gives me same results 18.39

SELECT CASE l.apr WHEN 0 THEN 0 ELSE l.draccr/(l.apr/12) END AS AAVGBALA

case when l.apr = 0 then 0 else

abs(l.draccr/(l.apr/1200))

result: 1855.26

when i run following indpendently, i get 4,113

DECLARE @draccr MONEY, @apr MONEY

SET @draccr = 23.65

SET @apr = 0.069

SELECT CASE @apr WHEN 0 THEN 0 ELSE @draccr/(@apr/12) END AS AAVGBALA

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

Do this to see:

SELECT CASE l.apr WHEN 0 THEN 0 ELSE l.draccr/(l.apr/12) END AS AAVGBALA, l.draccr, l.apr