I am trying to see what percentage of customers paying LL and the percentage of customers that are paying LP. For example I have 1 customer in LL and 1 in LP so percentage should be 50% in Cust_LL_Pct and Cust_LP_Pct. I have tried using a ISNULL, and set it as 0, but that didn’t fix the problem. The Pct Columns are giving be only 0 and 1, nothing in between. enter image description here
[SELECT b.country_IBS AS Country_IBS_LL,b.Sku AS SKU_LL,b.TY_Sales_USD_LL ,b.TY_Qty_LL,b.TY_Sales_USD_LL / (b.TY_Qty_LL) AS ASP_LL,b.Customer_Count_LL,a.country_IBS AS Country_IBS_LP,a.Sku AS SKU_LP,a.Total_LP_Sales,a.Total_LP_Qty,a.Total_LP_Sales / a.Total_LP_Qty AS ASP_LP,a.Customer_Count_LP,SUM(ISNULL(b.Customer_Count_LL,0) + ISNULL(a.Customer_Count_LP,0)) AS SKU_Total_Customer,ISNULL(b.Customer_Count_LL,0) / SUM(ISNULL(b.Customer_Count_LL,0) + ISNULL(a.Customer_Count_LP,0)) AS Cust_LL_Pct,ISNULL(a.Customer_Count_LP,0) / SUM(ISNULL(b.Customer_Count_LL,0) + ISNULL(a.Customer_Count_LP,0)) AS Cust_LP_Pct--INTO #temp6FROM #temp2 aFULL JOIN #temp5 bON a.Sku = b.SkuAND a.country_IBS = b.country_IBSGROUP BYb.country_IBS,b.Sku,b.TY_Sales_USD_LL,b.TY_Qty_LL,b.TY_Sales_USD_LL / b.TY_Qty_LL ,b.Customer_Count_LL,a.country_IBS,a.Sku,a.Total_LP_Sales,a.Total_LP_Qty,a.Total_LP_Sales / a.Total_LP_Qty ,a.Customer_Count_LP][1]Advertisement
Answer
The problem is integer division. When both operands are integers, SQL Server produces an integer result: for example, 3/2 yields 1, not 1.5.
You need to force decimal context for the operation, for example by multiplying with a (dummy) decimal value, like:
1.0 * b.TY_Sales_USD_LL / b.TY_Qty_LL AS ASP_LLI would not recommend using isnull(..., 0) for the right operand of a division: if the value is actually null, you get a division by 0, which is a fatal error. On the other hand, dividing by null yields null, which does not fail, and seems more relevant.