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 #temp6 FROM #temp2 a FULL JOIN #temp5 b ON a.Sku = b.Sku AND a.country_IBS = b.country_IBS GROUP BY b.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_LL
I 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.