asked
Profit calculation for specific year
Respected Techie, May anyone please help me how to achieve the below scenario. i am trying to calculate sales growth and margin growth to calculate the growth rate i am following the following step. **FOR YEAR 2016 ROWS.** based on key column (month, sales_manager_code and sales group). when key column is **not matched** then alias column Sales_growth and Margin_rate will be 1. when key column **match** then Sales_growth = NetProductSales - [AOP Sales]) / NetProductSales Margin_rate = BasedProfit - [Average Cost]) / BasedProfit **FOR YEAR <> 2016 ROWS.** Those rows contain Sales_growth and Margin_rate column as 1 Table structure DECLARE @LTM TABLE ( Year VARCHAR (10), Month VARCHAR (10), SALES_MANAGER_CODE VARCHAR (50), SALES_GROUP VARCHAR (50), NetProductSales VARCHAR (50), BasedProfit VARCHAR (50) ) INSERT @LTM SELECT '2015', '10', '10', 'ARS', '126431.16', '17221.78' UNION ALL SELECT '2015', '10', '4', '4', '1247439.2', '387552.72' UNION ALL SELECT '2015', '8', '11', '0', '44518.18', '6694.61' UNION ALL SELECT '2015', '1', '6', 'P', '481390.14', '129360.98' UNION ALL SELECT '2015', '6', '4', '1', '688092', '192849.43' UNION ALL SELECT '2014', '11', '4', '2', '1399367.53', '357748.66' UNION ALL SELECT '2014', '12', '2', 'U', '1380836.65', '346844.19' SELECT * FROM @LTM -- PIOP DECLARE @PIOP TABLE ( Year VARCHAR (10), Month VARCHAR (10), [Sales Manager] VARCHAR (50), [Sales Group] VARCHAR (50), [AOP Sales] VARCHAR (50), [Average Cost] VARCHAR (50) ) INSERT @PIOP SELECT '2016', '1', '10', '4', '1729247.579', '417828.79' UNION ALL SELECT '2016', '1', '4', '4', '2594910.701', '646462.21' UNION ALL SELECT '2016', '1', '11', '0', '1583079.251', '381467.1233' UNION ALL SELECT '2016', '1', '6', 'P', '20629.11529', '731.5890687' UNION ALL SELECT '2016', '1', '4', '1', '3333400.817', '785075.8625' UNION ALL SELECT '2016', '1', '4', '2', '6103697.475', '1411948.28' UNION ALL SELECT '2016', '1', '7', 'RVX', '65542.70073', '17438.56048' UNION ALL SELECT '2016', '1', '11', 'X', '0' ,'0' SELECT * FROM @PIOP The Result set contain full data set from both the table.
I believe I have a solution for you, but I assumed you don't actually store all of your values as strings. In my case, I changed NetProductSales and BasedProfit to decimal(19,2), and I changed [AOP Sales] and [Average Cost] to decimal(19,5). I chose 19 because any precision between 10 and 19 requires the same 9 bytes of storage. Based on my understanding of your requirements, the only time the calculation would be used for the sample data would be for January with manager 6 in group P. SELECT Year = ISNULL(l.Year, p.Year) , Month = ISNULL(l.Month, p.Month) , [Sales Manager] = ISNULL(l.SALES_MANAGER_CODE, p.[Sales Manager]) , [Sales Group] = ISNULL(l.SALES_GROUP, p.[Sales Group]) , Sales_Growth = CONVERT(decimal(9, 2), CASE WHEN l.Year = 2016 OR p.Year = 2016 THEN CASE WHEN l.Year is not null AND p.Year is not null -- matching from both tables THEN (l.NetProductSales - p.[AOP Sales]) / l.NetProductSales ELSE 1 END ELSE 1 END ) , Margin_Rate = CONVERT(decimal(9, 2), CASE WHEN l.Year = 2016 OR p.Year = 2016 THEN CASE WHEN l.Year is not null AND p.Year is not null -- matching from both tables THEN (l.BasedProfit - p.[Average Cost]) / l.BasedProfit ELSE 1 END ELSE 1 END ) FROM @LTM l FULL JOIN @PIOP p ON p.Month = l.Month AND p.[Sales Manager] = l.SALES_MANAGER_CODE AND p.[Sales Group] = l.SALES_GROUP ;
That's exactly what I did. Did you try my code? The join matches on month, manager, and group. The outer CASE checks if either table year value is 2016. If not, return 1. The inner CASE checks if you have data matching per the join. If not, return 0. Otherwise run the calculation. Unless I'm misunderstanding your requirements, this should do exactly what you want.
I changed the 0s to 1s. Thats the only change necessary according to your updated instructions. Most of 2016 now returns 1 instead of the calculation because only 1 row matches on the key (month, manager, and group).
18 People are following this question.
