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

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

Cookies on SQLServerCentral

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