fahVB
asked on
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*(3 65/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
select stmt
case when l.apr = 0 then 0 else abs(l.draccr/(l.apr/365*(3
here is an excel formula, which works
=+B4/(B3/365*(365/12))
B4 = l.draccr
B3 = l.apr
any ideas please
ASKER
Same results,
in Excel i m getting 4113.00
in sql 18.39
in Excel i m getting 4113.00
in sql 18.39
What are the numbers you are dealing with. Check order of operations as that is a huge swing in results.
ASKER
can you please be more specific? which numbers and order of operation
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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
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
Try this:
case when l.apr = 0 then 0 else
abs(l.draccr/(l.apr/1200)) end as AAVGBALA,
case when l.apr = 0 then 0 else
abs(l.draccr/(l.apr/1200))
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
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.
Anyway, hopefully this is helping.
ASKER
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
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))
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
darn me, you guys are right...data was wrong....sorry for the trouble...its all good now...
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:
Open in new window