Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2003
    Location
    Wimbledon
    Posts
    10

    Unanswered: SQL Problem when doing percentage

    Hi

    I am trying to calculate a percentage using the following SQL :

    SELECT
    DBP1.VDWMCRD_CREDITS.CUST_ACC_NUM,
    DBP1.VDWMCRD_CREDITS.CUST_HOME_BR,
    DBP1.VDWMCRD_CREDITS.STMT_YEAR,
    DBP1.VDWMCRD_CREDITS.STMT_MONTH,
    Sum((DBP1.VDWMCRD_CREDITS.SALES_VALUE+1)*-1) AS CREDIT_VALUE,
    Sum(DBP1.VDWMSLS_SALES.SALES_VALUE) AS SLS_VALUE,
    DBP1.VDWMBRA_BRANCH.BRANCH_NAME,
    DBP1.VDWMCUS_CUST_ACC.CUSTOMER_NAME,
    float(float(sum(DBP1.VDWMCRD_CREDITS.SALES_VALUE*-1)+1)
    /
    float(sum((DBP1.VDWMCRD_CREDITS.SALES_VALUE*-1)+(DBP1.VDWMSLS_SALES.SALES_VALUE)))) AS PERCENT_OF_SLS
    FROM
    DBP1.VDWMCUS_CUST_ACC INNER JOIN DBP1.VDWMBRA_BRANCH INNER JOIN DBP1.VDWMCRD_CREDITS ON DBP1.VDWMBRA_BRANCH.BRANCH_NUM = DBP1.VDWMCRD_CREDITS.CUST_HOME_BR ON DBP1.VDWMCUS_CUST_ACC.CUST_ACC_NUM = DBP1.VDWMCRD_CREDITS.CUST_ACC_NUM INNER JOIN DBP1.VDWMSLS_SALES ON DBP1.VDWMCRD_CREDITS.CUST_ACC_NUM = DBP1.VDWMSLS_SALES.CUST_ACC_NUM AND DBP1.VDWMCRD_CREDITS.PRODUCT_TYPE = DBP1.VDWMSLS_SALES.PRODUCT_TYPE AND DBP1.VDWMCRD_CREDITS.STMT_YEAR = DBP1.VDWMSLS_SALES.STMT_YEAR AND DBP1.VDWMCRD_CREDITS.STMT_MONTH = DBP1.VDWMSLS_SALES.STMT_MONTH AND DBP1.VDWMCRD_CREDITS.CUST_HOME_BR = DBP1.VDWMSLS_SALES.CUST_HOME_BR
    WHERE
    DBP1.VDWMCRD_CREDITS.CUST_HOME_BR='101'
    AND
    DBP1.VDWMCRD_CREDITS.STMT_YEAR=2003
    AND
    DBP1.VDWMCRD_CREDITS.STMT_MONTH=7
    GROUP BY
    DBP1.VDWMCRD_CREDITS.CUST_ACC_NUM,
    DBP1.VDWMCRD_CREDITS.CUST_HOME_BR,
    DBP1.VDWMCRD_CREDITS.STMT_YEAR,
    DBP1.VDWMCRD_CREDITS.STMT_MONTH,
    DBP1.VDWMBRA_BRANCH.BRANCH_NAME,
    DBP1.VDWMCUS_CUST_ACC.CUSTOMER_NAME
    HAVING
    Sum(DBP1.VDWMCRD_CREDITS.SALES_VALUE) <> 0 AND
    Sum(DBP1.VDWMSLS_SALES.SALES_VALUE) <> 0;

    We are trying to show the credits as a percentage of the sales value.
    The sales value is net of credits, so that is why we are multiplying the credit value (stored as negative) by -1 to make it positive, and then adding it to the sales value to get the gross sales value.
    Under the having we have tried to exclude divide by 0s.

    However, when the results return we get 0 for all of the percentage values which is definitely not correct.
    We believe that when DB2 returns the results it is returning the percentage value as an integer rather than a floating point number.

    The underlying reason why we are doing this is to get the Top 15 customers based on the % of credits to sales.

    Does anyone know a way around this or can you advise on any problems with above SQL?


    Thanks for any help you can provide

    Brendan

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: SQL Problem when doing percentage

    Multiply by 100 to get percentage value ..

    HTH
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •