Post reply

SQL Calculation

  • November 13, 2012 at 8:07 am

    #266947

    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

    #1558526

    post removed, Better solution below......

    _________________________________________________________________________
    SSC Guide to Posting and Best Practices

  • November 13, 2012 at 9:00 am

    #1558527

    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

    #1558530

    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

    #1558534

    Thanks so much. Works perfectly. Scott

  • November 13, 2012 at 11:24 am

    #1558586

    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

    #1585361

    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

    #1585693

    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

    #1585786

    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

    #1585792

    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 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply