May 24, 2012 at 3:49 pm
I am referring to this article.
http://www.sqlservercentral.com/articles/T-SQL/77391/
I was expecting to get consistent results between these two select statements. Am I misunderstanding something?
To add to my confusion the Excel PRODUCT function returns the first result.
When I query my actual data from which the sample data was derived, using the calc in my first select statement the second value is returned.
I believe the first result is correct. Why because it matches a vendor provided calc that i am trying to reverse engineer. They suggested the Excel PRODUCT function.
So to summarize
1) If I use the COALESCE...function described in the article against sample data I get the correct result
2) My select statement where I hard code the numbers returns the incorrect result
3) The PRODUCT function in excel returns the correct calc
4) The COALESCE... function against my production data from which I derived my sample data returns the incorrect figure.
Can someone set me straight?
CREATE TABLE #Test (Test decimal(12,8))
INSERT INTO #Test
SELECT 1.0972176 UNION
SELECT -1.985984 UNION
SELECT -2.0289558 UNION
SELECT 1.2231256 UNION
SELECT 1.2133187 UNION
SELECT 0.3053794 UNION
SELECT -0.5361731 UNION
SELECT 2.8730031 UNION
SELECT 2.1042076 UNION
SELECT 0.9360069 UNION
SELECT -0.1588461 UNION
SELECT -0.2703052 UNION
SELECT -0.4338919 UNION
SELECT -0.8498999 UNION
SELECT -0.6554889 UNION
SELECT -0.2889361
SELECT
COALESCE(EXP(SUM(LOG(ABS(NULLIF(Test, 0))))),0) * (1 - 2 * (COUNT(CASE WHEN Test < 0 THEN 1 END) % 2))
FROM #Test
DROP TABLE #Test
SELECT
1.010972176 *
0.98014016 *
0.979710442 *
1.012231256 *
1.012133187 *
1.003053794 *
0.994638269 *
1.028730031 *
1.021042076 *
1.009360069 *
0.998411539 *
0.997296948 *
0.995661081 *
0.991501001 *
0.993445111 *
0.997110639
May 24, 2012 at 4:13 pm
Chrissy321 (5/24/2012)
I was expecting to get consistent results between these two select statements. Am I misunderstanding something?
The data does not match between the table and hard-coded statement :unsure:
CREATE TABLE #Test (Test decimal(12,8))
INSERT INTO #Test
SELECT 1.0972176 UNION ALL
SELECT -1.985984 UNION ALL
SELECT -2.0289558 UNION ALL
SELECT 1.2231256 UNION ALL
SELECT 1.2133187 UNION ALL
SELECT 0.3053794 UNION ALL
SELECT -0.5361731 UNION ALL
SELECT 2.8730031 UNION ALL
SELECT 2.1042076 UNION ALL
SELECT 0.9360069 UNION ALL
SELECT -0.1588461 UNION ALL
SELECT -0.2703052 UNION ALL
SELECT -0.4338919 UNION ALL
SELECT -0.8498999 UNION ALL
SELECT -0.6554889 UNION ALL
SELECT -0.2889361
SELECT
COALESCE(EXP(SUM(LOG(ABS(NULLIF(Test, 0))))),0) * (1 - 2 * (COUNT(CASE WHEN Test < 0 THEN 1 END) % 2))
FROM #Test
SELECT
1.0972176 *
-1.985984 *
-2.0289558 *
1.2231256 *
1.2133187 *
0.3053794 *
-0.5361731 *
2.8730031 *
2.1042076 *
0.9360069 *
-0.1588461 *
-0.2703052 *
-0.4338919 *
-0.8498999 *
-0.6554889 *
-0.2889361
DROP TABLE #Test
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 24, 2012 at 4:16 pm
How embarassing! Sorry to be a nuisance.
May 24, 2012 at 4:21 pm
I wish there was a 'retract my post function'....
May 24, 2012 at 5:57 pm
Chrissy321 (5/24/2012)
How embarassing! Sorry to be a nuisance.
Not at all. We've all done it.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply