We help IT Professionals succeed at work.

sql select stmt formula not working

fahVB
fahVB asked
on
264 Views
Last Modified: 2011-10-19
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
Comment
Watch Question

Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
Remember SQL defaults to integer division when dealing with int values, so if you depend on any of the equation to evaluate to a decimal it is probably not producing the results you want as it is rounding to integer result.

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

Open in new window

Author

Commented:
Same results,

in Excel i m getting 4113.00
in sql 18.39
Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
What are the numbers you are dealing with.  Check order of operations as that is a huge swing in results.

Author

Commented:
can you please be more specific? which numbers and order of operation
Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
What are the values of ?
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.
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
in this instance i am just testing on one member

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
Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
Ok, so you are just doing B4/(B3/12)

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

Commented:
Try this:

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




Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
Make sure those fields are setup as an appropriate data type like MONEY.
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

Open in new window

Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
DGMG's suggestion will work if you have your data stored as 6.9 instead of 0.069, but still not sure how you were getting 18.39 before as having 6.9 instead just lowers the number to 41.13 instead of 4,113.

Anyway, hopefully this is helping.

Author

Commented:
not working
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)) end as AAVGBALA,

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


 


Chief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
darn me, you guys are right...data was wrong....sorry for the trouble...its all good now...

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.