Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 SQL Calculation Rate Topic Display Mode Topic Options
Author
 Message
 Posted Tuesday, November 13, 2012 8:07 AM
 SSC Journeyman Group: General Forum Members Last Login: Friday, May 10, 2013 3:24 AM Points: 83, Visits: 277
 Hi all. I hope you can lend a hand to my sore head!I have to write a query to work out a running multiplication by a factor! ouch.Start number = 12. Call that xID Factor1 1.12 1.11 1.12 1.2So once group the answer should beID Result1 14.52 ((12 * 1.1) = 13.2 then (13.2 * 1.1) = 14.512 15.84 ((12 * 1.1) = 13.2 then (13.2 * 1.2) = 15.84I have been looking at http://mangalpardeshi.blogspot.co.uk/2009/06/multiplying-column-values.html but this just adds the multiplied field onto each other.I will keep trying in the meantime. Thanks for any pointers. Scott
Post #1384117
 Posted Tuesday, November 13, 2012 8:57 AM
 SSC Eights! Group: General Forum Members Last Login: 2 days ago @ 1:30 AM Points: 803, Visits: 2,124
 post removed, Better solution below...... _________________________________________________________________________SSC Guide to Posting and Best Practices
Post #1384135
 Posted Tuesday, November 13, 2012 9:00 AM
 SSCarpal Tunnel Group: General Forum Members Last Login: Today @ 8:44 AM Points: 4,434, Visits: 7,218
 Or use logarithms:`CREATE TABLE #john (ID int, Factor decimal(5,3))INSERT INTO #john VALUES (1,1.1),(2,1.1),(1,1.1),(2,1.2)SELECT * FROM #johnDECLARE @x decimal(5,3)SET @x = 12SELECT ID, EXP(SUM(LOG(Factor))) * @x AS AnswerFROM #johnGROUP BY ID`John
Post #1384136
 Posted Tuesday, November 13, 2012 9:01 AM
 SSC Eights! Group: General Forum Members Last Login: 2 days ago @ 1:30 AM Points: 803, Visits: 2,124
 John Mitchell-245523 (11/13/2012)Or use logarithms:`CREATE TABLE #john (ID int, Factor decimal(5,3))INSERT INTO #john VALUES (1,1.1),(2,1.1),(1,1.1),(2,1.2)SELECT * FROM #johnDECLARE @x decimal(5,3)SET @x = 12SELECT ID, EXP(SUM(LOG(Factor))) * @x AS AnswerFROM #johnGROUP BY ID`John+1, Nice use of the EXP and LOG functions that I'd forgotten all about. _________________________________________________________________________SSC Guide to Posting and Best Practices
Post #1384139
 Posted Tuesday, November 13, 2012 9:13 AM
 SSC Journeyman Group: General Forum Members Last Login: Friday, May 10, 2013 3:24 AM Points: 83, Visits: 277
 Thanks so much. Works perfectly. Scott
Post #1384148
 Posted Tuesday, November 13, 2012 10:20 AM
 SSCommitted Group: General Forum Members Last Login: Tuesday, January 15, 2013 11:11 AM Points: 1,945, Visits: 2,782
 If this were a ANSI built-in the name would be PRD(), followign the pattern for SUM(), AVG() etc. Here is a version of the aggregate product function in SQL. You will need to have the logarithm and exponential functions. The idea is that there are three special cases - all positive numbers, one or more zeros, and some negative numbers in the set. You can find out what your situation is with a quick test on the sign() of the minimum value in the set. Within the case where you have negative numbers, there are two sub-cases: (1) an even number of negatives or (2) an odd number of negatives. You then need to apply some High School algebra to determine the sign of the final result. SELECT CASE MIN (SIGN(nbr)) WHEN 1 THEN EXP(SUM(LN(nbr))) -- all positive numbers WHEN 0 THEN 0.00 -- some zeroes WHEN -1 -- some negative numbers THEN (EXP(SUM(LN(ABS(nbr)))) * (CASE WHEN MOD (SUM(ABS(SIGN(nbr)-1)/ 2)), 2) = 1 THEN -1.00 ELSE 1.00 END) ELSE NULL END AS big_pi FROM Foobar;SELECT CASE MIN(ABS(SIGN(nbr))) WHEN 0 THEN 0.00 -- some zeroes ELSE -- no zeroes EXP(SUM(LOG(ABS(NULLIF(nbr, 0))))) * CASE WHEN MOD (CAST(SUM(ABS(SIGN(nbr)-1)/2) AS INTEGER), 2) = 1 THEN -1.00 ELSE 1.00 ENDEND AS big_piAs an aside, the book BYPASSES:A SIMPLE APPROACH TO COMPLEXITY by Z. A. Melzak (Wiley-Interscience, 1983, ISBN 0-471-86854-X), is short mathematical book on the general principle of conjugacy. This is the method of using a transform and its inverse to reduce the complexity of a calculation. Books in Celko Series for Morgan-Kaufmann PublishingAnalytics and OLAP in SQL Data and Databases: Concepts in Practice Data, Measurements and Standards in SQLSQL for SmartiesSQL Programming Style SQL Puzzles and Answers Thinking in SetsTrees and Hierarchies in SQL
Post #1384183
 Posted Tuesday, November 13, 2012 11:24 AM
 SSC Journeyman Group: General Forum Members Last Login: Friday, May 10, 2013 3:24 AM Points: 83, Visits: 277
 Thanks for the additional information. It's helpful to receive and be able to look further into the process.Many thanks scott.
Post #1384213
 Posted Thursday, February 07, 2013 5:37 AM
 SSC Journeyman Group: General Forum Members Last Login: Friday, May 10, 2013 3:24 AM Points: 83, Visits: 277
 This function has been working 100% in production for some time. Now I need to factor down as well i.e. 0.9Currently this actually makes the result 0.I will be looking into this and report back once I have a solution.Scott
Post #1416995
 Posted Thursday, February 07, 2013 4:41 PM
 SSCarpal Tunnel Group: General Forum Members Last Login: Today @ 2:38 PM Points: 4,557, Visits: 8,213
 It does not do it for me.Can you post your actual data to use with the test script above?
Post #1417388
 Posted Friday, February 08, 2013 1:37 AM
 SSC Journeyman Group: General Forum Members Last Login: Friday, May 10, 2013 3:24 AM Points: 83, Visits: 277
 I had one of those thoughts dropping off last night. I think maybe it might be due to the datatype.I will try it out and if not then post my code. Thank for your input. Scott
Post #1417505

 Permissions