I am trying to calculate a percentage using the following SQL :
Sum((DBP1.VDWMCRD_CREDITS.SALES_VALUE+1)*-1) AS CREDIT_VALUE,
Sum(DBP1.VDWMSLS_SALES.SALES_VALUE) AS SLS_VALUE,
float(sum((DBP1.VDWMCRD_CREDITS.SALES_VALUE*-1)+(DBP1.VDWMSLS_SALES.SALES_VALUE)))) AS PERCENT_OF_SLS
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
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?