Solved

# Sql query Average balance

Posted on 2008-10-06
237 Views
I am trying to get an avearge balance within my sql select stmt and getting back a different number, i do this on a calculator and a number match

based on this logic
Finance charge divide it by the APR divided by 12 i should get average balance of 14,862.09

Finance charge=184.29
Apr = 14.88

my select stmt
cast(case when e.interestrate = 0 then 0 else abs(l.fin_chrg/(e.interestrate/12)) end as decimal(6,2)) as AAVGBALA,

gives me 148.62, but i should be getting 14,862.09

e.interestrate  = apr
l.fin_chrg - finance charge

0
Question by:fahVB
• 2
• 2
• 2
• +1

LVL 60

Expert Comment

ID: 22653702
are you dividing by 100 anywhere?  I can't see that you are, but I can't look at  your values either...
0

LVL 1

Expert Comment

ID: 22653705
It looks like it's getting confused with the percentage... try this and see how it looks:

cast(case when e.interestrate = 0 then 0 else abs(l.fin_chrg/(e.interestrate/12))*100 end as decimal(6,2)) as AAVGBALA,
0

LVL 142

Expert Comment

ID: 22653713
I guess you are missing a multiplication factor of 100?!
0

Author Comment

ID: 22653751
yes, when *100 i get following error

Msg 8115, Level 16, State 6, Line 10
Arithmetic overflow error converting float to data type numeric.

(0 row(s) affected)

0

LVL 1

Expert Comment

ID: 22653770
Is your data type set to percentage? That would cause it to divide by 100.
0

LVL 142

Accepted Solution

Guy Hengel [angelIII / a3] earned 500 total points
ID: 22653837
you will need to use
cast(case when e.interestrate = 0 then 0 else abs(l.fin_chrg/(e.interestrate/12)) end as decimal(16,2)) as AAVGBALA,

instead. because otherwise decimal(6,2) will only allow numbers up to 9999.99
0

Author Closing Comment

ID: 31503582
darn me, you guys are good. It worked
0

## Join & Write a Comment Already a member? Login.

### Suggested Solutions

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

#### 744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

#### Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!