Solved

sql select stmt formula not working

Posted on 2008-10-04
14
234 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
0
Comment
Question by:fahVB
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 2
14 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22641745
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

0
 

Author Comment

by:fahVB
ID: 22641762
Same results,

in Excel i m getting 4113.00
in sql 18.39
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22641805
What are the numbers you are dealing with.  Check order of operations as that is a huge swing in results.
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

Author Comment

by:fahVB
ID: 22641834
can you please be more specific? which numbers and order of operation
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22641852
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.
0
 
LVL 42

Assisted Solution

by:dqmq
dqmq earned 40 total points
ID: 22641890
Are you sure this is correctly copied from excel?

=+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?


0
 

Author Comment

by:fahVB
ID: 22641937
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
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22642032
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
0
 
LVL 42

Expert Comment

by:dqmq
ID: 22642039
Try this:

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




0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22642047
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

0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22642052
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.
0
 

Author Comment

by:fahVB
ID: 22642072
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


 


0
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 460 total points
ID: 22642100
That means your data in the SQL tables are not coming out correctly OR at least are not coming back with same values of 23.65 and 0.069.  

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
0
 

Author Closing Comment

by:fahVB
ID: 31503068
darn me, you guys are right...data was wrong....sorry for the trouble...its all good now...
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

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

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

Join & Ask a Question