November 13, 2012 at 8:07 am
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 x
ID Factor
1 1.1
2 1.1
1 1.1
2 1.2
So once group the answer should be
ID Result
1 14.52 ((12 * 1.1) = 13.2 then (13.2 * 1.1) = 14.51
2 15.84 ((12 * 1.1) = 13.2 then (13.2 * 1.2) = 15.84
I 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
November 13, 2012 at 8:57 am
post removed, Better solution below......
_________________________________________________________________________
SSC Guide to Posting and Best Practices
November 13, 2012 at 9:00 am
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 #john
DECLARE @x decimal(5,3)
SET @x = 12
SELECT
ID
,EXP(SUM(LOG(Factor))) * @x AS Answer
FROM
#john
GROUP BY
ID
John
November 13, 2012 at 9:01 am
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 #john
DECLARE @x decimal(5,3)
SET @x = 12
SELECT
ID
,EXP(SUM(LOG(Factor))) * @x AS Answer
FROM
#john
GROUP BY
ID
John
+1, :blush: Nice use of the EXP and LOG functions that I'd forgotten all about.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
November 13, 2012 at 9:13 am
Thanks so much. Works perfectly. Scott
November 13, 2012 at 11:24 am
Thanks for the additional information. It's helpful to receive and be able to look further into the process.
Many thanks scott.
February 7, 2013 at 5:37 am
This function has been working 100% in production for some time. Now I need to factor down as well i.e. 0.9
Currently this actually makes the result 0.
I will be looking into this and report back once I have a solution.
Scott
February 7, 2013 at 4:41 pm
It does not do it for me.
Can you post your actual data to use with the test script above?
_____________
Code for TallyGenerator
February 8, 2013 at 1:37 am
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
February 8, 2013 at 1:53 am
Yes. Confirmed. Was a datatype I was capturing to take care of zeros. Was set to int therefore 0.9 came out as 0.
Had we worried for a minute! Thanks again Scott
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy