Post reply

Help with an Equation...?

  • June 30, 2005 at 6:59 am

    #164144

    I have this SQL statement that I am trying to show the correct response...

    First, here is the line that I am working on:

    [Sales to Referrals Ratio] = CAST((CAST(sum(CASE WHEN PRODUCTS_SOLD_1 <> '' THEN 1 ELSE 0 END) AS decimal(10,5)) + CAST(sum(CASE WHEN PRODUCTS_SOLD_2 <> '' THEN 1 ELSE 0 END)AS decimal(10,5)) / CAST(COUNT(*) AS decimal(10,5))) as decimal(10,5))

    Now the values that appear for each part of this equation are as follows:

    [Sales to Referrals Ratio] = (1 + 0) / 40

    When I run this....I get a response of '1', where I should be getting '.025'

    Even with the CAST, to a decimal, I have been unable to get it to show the correct number. I will then want to multiply this decimal by 100 so as to get a percentage...

    Does anyone see where I am going wrong?

    Thank you..

  • June 30, 2005 at 7:12 am

    #569762

    This should work:

    [Sales to Referrals Ratio] = SUM(CASE WHEN PRODUCTS_SOLD_1 '' THEN 1.0 ELSE 0.0 END) + SUM(CASE WHEN PRODUCTS_SOLD_2 '' THEN 1.0 ELSE 0.0 END) / COUNT(*)

    --
    Chris Hedgate http://www.hedgate.net/
    Contributor to the Best of SQL Server Central volumes
    Articles: http://www.sqlservercentral.com/columnists/chedgate/

  • June 30, 2005 at 7:18 am

    #569765

    Thank you for your reply...but it still giving me an answer of '1'...Mathematically when I divide 1 by 40 I should have a decimal of .025, which when translated into a percentage would be 25%...

    This is what I am attemtpting to do with my equation...but I can not see what I am missing????

  • June 30, 2005 at 7:20 am

    #569766

    If I did this correctly, after removing all the casts and sums your statement comes out to be

    1 + 0 / 40

    and of couse the 0 / 40 gets done first leaving you with 0 and then 1+ 0 = 1.

    Edit: What you need is (1 + 0) / 40

  • June 30, 2005 at 7:21 am

    #569768

    Always the small things that trip you up .

  • June 30, 2005 at 7:24 am

    #569772

    You know...it is those little things that will drive you to drink!

    Wait....I am all reeedy drinkkkng...

    THANKS!!!

  • June 30, 2005 at 7:27 am

    #569775

    I'm glad I don't have to drink a lot recently .

  • June 30, 2005 at 2:18 pm

    #570146

    Well no, you don't HAVE to..., that is a wonderful choice we can make...  

    (ooops..., is my Irish showing?)

    I wasn't born stupid - I had to study.

  • June 30, 2005 at 2:20 pm

    #570148

    Yup. Or just the fact that you're a guy and it's hot out there

Viewing 9 posts - 1 through 8 (of 8 total)

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