Calculations

  • Hello,

    I need some help please. I have 2 temp tables like so

    CREATE TABLE #fSales

    (

    season int,

    Sales Money,

    products int,

    productpurchase int

    )

    insert into #fSales

    values(1, 200, 300, 150)

    insert into #fSales

    values(2, 250,500, 200)

    insert into #fSales

    values(3, 300, 400, 400)

    insert into #fSales

    values(4, 400,330, 310)

    insert into #fSales

    values(5, 500, 200, 110)

    CREATE TABLE #pSales

    (

    season int,

    Sales Money,

    products int,

    productpurchase int

    )

    insert into #pSales

    values(1, 200, 250, 100)

    insert into #pSales

    values(2, 250, 450, 150)

    insert into #pSales

    values(3, 300, 350, 350)

    insert into #pSales

    values(4, 400, 280, 260)

    insert into #pSales

    values(5, 500, 150, 60)

    is there a way to calculate the percentage increase/decrease of #fsales.products compared to #psales.products and also the same thing for #fsale.productspurchase compared to #psale.productspurchase. Do i need to change the datatype to decimal? Thanks

  • Yes - you will have to change the datatype to decimal (unless you specifically need float).

    Assuming you want to track the % increase between products and productspurchase for the same season - you'll have to join the two tables on season and compute the % increase...the code below is based on my understanding of how % increase between two values is computed and will hopefully point you in the right direction

    SELECT f.season,f.products,p.products,f.productpurchase,p.productpurchase,

    CASE WHEN f.products >= p.products THEN 'FProducts%Increase:'+

    CAST((((f.products-p.products)/p.products)*100) as varchar(30))

    ELSE 'PProducts%Increase:'+CAST((((p.products-f.products)/f.products)*100) as varchar(30))

    END ProductsPercentChanges,

    CASE WHEN f.productpurchase >= p.productpurchase THEN 'FProductPurchase%Increase:'+

    CAST((((f.productpurchase-p.productpurchase)/p.productpurchase)*100) as varchar(30))

    ELSE 'PProductPurchase%Increase:'+CAST((((p.productpurchase-f.productpurchase)/f.productpurchase)*100) as varchar(30))

    END ProductsPercentChanges

    FROM #fSales f INNER JOIN #pSales p ON

    f.season=p.season

  • You can "cheat" a bit on this type of code...

    SELECT STR((23-20)*100.0/20,5,1)+'%'

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Nice!! I never thought of using STR()...I can now use this in a lot of places...Thanks Jeff!

  • Your welcome but a word of caution... if a GUI is involved, formatting of this nature should be done in the GUI. And, never store formatted numeric data in a database... it just takes extra space and you have to "unformat" it to use it in mathematical calculations in most cases. That's a huge waste of time.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thanks for the help, I wrote something basic to solve it but this is just very amazing guys. Thanks again!

  • npatel565 (8/20/2009)


    Thanks for the help, I wrote something basic to solve it but this is just very amazing guys. Thanks again!

    Would you mind sharing the "something basic" please? Two way street here... 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 7 posts - 1 through 6 (of 6 total)

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