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

sql select stmt formula not working

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
fahVB
Asked:
fahVB
  • 7
  • 5
  • 2
2 Solutions
 
Kevin CrossChief Technology OfficerCommented:
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
 
fahVBAuthor Commented:
Same results,

in Excel i m getting 4113.00
in sql 18.39
0
 
Kevin CrossChief Technology OfficerCommented:
What are the numbers you are dealing with.  Check order of operations as that is a huge swing in results.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
fahVBAuthor Commented:
can you please be more specific? which numbers and order of operation
0
 
Kevin CrossChief Technology OfficerCommented:
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
 
dqmqCommented:
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
 
fahVBAuthor 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
0
 
Kevin CrossChief Technology OfficerCommented:
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
 
dqmqCommented:
Try this:

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




0
 
Kevin CrossChief Technology OfficerCommented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
 
fahVBAuthor 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


 


0
 
Kevin CrossChief Technology OfficerCommented:
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
 
fahVBAuthor Commented:
darn me, you guys are right...data was wrong....sorry for the trouble...its all good now...
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

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