question

AlexKlap avatar image
AlexKlap 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.
t-sqlsql-server-2012sql-server-2014
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Tom Staab avatar image
Tom Staab answered
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 ;
6 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks a lot Tom, but i am looking when key column month,SALES_MANAGER_CODE,sales_group matched then calculate sales and margin. when not matched then 0 for year 2016 other than year 2016, sales and margin will be 1. Please hellp
0 Likes 0 ·
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.
0 Likes 0 ·
Hi Tom, first of all Thanks a lot. and i am sorry, as i feel my question was not clear. i have edited the question in point. when i tried the code it was giving 0 for year 2016 instead of calculating growth. Please help.
0 Likes 0 ·
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).
0 Likes 0 ·
Thank you Tom
0 Likes 0 ·
Show more comments

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.