• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 255
  • Last Modified:

Sql query Average balance

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
fahVB
Asked:
fahVB
  • 2
  • 2
  • 2
  • +1
1 Solution
 
chapmandewCommented:
are you dividing by 100 anywhere?  I can't see that you are, but I can't look at  your values either...
0
 
Chris BrockChief Information OfficerCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I guess you are missing a multiplication factor of 100?!
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
fahVBAuthor Commented:
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
 
Chris BrockChief Information OfficerCommented:
Is your data type set to percentage? That would cause it to divide by 100.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
fahVBAuthor Commented:
darn me, you guys are good. It worked
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now