February 24, 2012 at 9:00 am
Morning,
I'm having an issue averaging a turn time.
SELECT dbo.fn_Business_Days_Between_Dates(dbo.GetLocalDateTimeFunc(FM.OpenedDate),dbo.GetLocalDateTimeFunc(FA.receiveddate))
This statement returns the following:
3
6
3
3
0
4
6
4
6
0
NULL
NULL
This adds up to 35. Since AVG is supposed to ignore NULL values, 35/10=3.5
However, when I run the following, I get 3
SELECT AVG(dbo.fn_Business_Days_Between_Dates(dbo.GetLocalDateTimeFunc(FM.OpenedDate),dbo.GetLocalDateTimeFunc(FA.receiveddate)))
Any ideas on why its not averaging correctly? Is it possible that it is just returning the first number from the original query, which is 3? Thanks.
February 24, 2012 at 9:03 am
One possible solution ...
SELECT
AVG(dbo.fn_Business_Days_Between_Dates(dbo.GetLocalDateTimeFunc(FM.OpenedDate),dbo.GetLocalDateTimeFunc(FA.receiveddate))*1.0)
🙂
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgFebruary 24, 2012 at 9:12 am
Jason Selburg (2/24/2012)
One possible solution ...
SELECT
AVG(dbo.fn_Business_Days_Between_Dates(dbo.GetLocalDateTimeFunc(FM.OpenedDate),dbo.GetLocalDateTimeFunc(FA.receiveddate))*1.0)
🙂
Wow. That worked. I now have 3.500000. Is the built in function just not displaying any decimals, then the average function stripped the decimals off completely?
February 24, 2012 at 9:29 am
Jeremy... (2/24/2012)
Jason Selburg (2/24/2012)
One possible solution ...
SELECT
AVG(dbo.fn_Business_Days_Between_Dates(dbo.GetLocalDateTimeFunc(FM.OpenedDate),dbo.GetLocalDateTimeFunc(FA.receiveddate))*1.0)
🙂
Wow. That worked. I now have 3.500000. Is the built in function just not displaying any decimals, then the average function stripped the decimals off completely?
I assume your function returns an INT datatype, and the average of INTs return and INT which has a precision of 0 (no decimal places).
By multiplying by 1.0 you cause an explicit conversion to DECIMAL(38,6), thus giving you the correct result.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgFebruary 24, 2012 at 9:43 am
Thanks much Jason. have a great weekend.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply