Respected Techie,
May anyone please help me how to achieve the below scenario.
The resultset contain full data from both the table
I am trying to calculate the difference between ( (NetProductSales - AOP Sales)/NetProductSales) as Sales_growth
and similarly ((BasedProfit-Average Cost)/BasedProfit) as Margin_rate
For the year 2015 and 2014 sales growth and margin will be 1
the above calculation is only for year 2016
The calculation will be done when month, sales manager and sales group will be match
whenever any one of this is not matching then Sales_growth = 0 and margin =0
*/
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