SQLTeam.com | Weblogs | Forums

Profit calculation for specific year

sql2014
sql2008

#1

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


#2
SELECT a.[Year], a.[Month], a.[Sales_Group], a.[SALES_MANAGER_CODE],
ISNULL(SUM(a.[NetProductSales] - b.[AOP Sales]/a.[NetProductSales]),0)  as Sales_growth,
ISNULL(SUM(a.[BasedProfit] - b.[Average Cost]/a.[BasedProfit]), 0) as Margin_rate
FROM @LTM a 
LEFT JOIN @PIOP b
ON  a.[SALES_MANAGER_CODE] = b.[Sales Manager]
					AND a.[SALES_GROUP] = b.[Sales Group]
					AND a.[Month] = b.[Month]
					GROUP BY a.[SALES_GROUP], a.[SALES_MANAGER_CODE], a.[Month], a.[Year]