Respected Techie,
may some one please help me how to achieve this.
/*
I have a data as shown above.
I am trying to compare Sales value of year 2015 with sales value of 2016
and the difference stored in alias column as Sales_growth for year 2016 , for year 2015 the alias column be as '1'
similarly difference between margin of 2015 and 2016 and result stored in alias column as margin_rate in year 2016
for 2015 as 1
key column is to be (month, SM,SG,CUST,SP)
this I have achieve through self join
but when there is no record for year 2015
and record present in 2016 for a given (month, SM,SG,CUST,SP)
then the alias column sales_growth and margin_rate should be 100
Formula to calculate sales_growth and margin_rate
Sales_growth = (sales(2015)-sales (2016)) / Sales_growth(2015)
SGR = (3456.05-3603.33) /3456.05 = -0.043
margin_rate =( margin (2015)-margin( 2016) / margin(2015)
margin_rate = (1059.24-1053.07)/1059.24= 0.006
DECLARE @T1 TABLE
(
YEAR numeric (4,0),
MONTH numeric (2,0) ,
SM char (2),
SG varchar (10),
CUST numeric (10,0) ,
SP numeric (10,0),
SALES float ,
Margin float
)
INSERT @T1
SELECT 2015, 10, '9', 'NE', 9014489 ,6413 ,3456.05 ,1059.24 UNION ALL
SELECT 2016, 10, '9', 'NE', 9014489 ,6413 ,3603.33 ,1053.07 UNION ALL
SELECT 2015, 1, '9', 'NE', 9014489 ,6413 ,1394.58 ,106.55 UNION ALL
SELECT 2016, 1, '9', 'NE', 9014489 ,6413 ,1618.24 ,548.38 UNION ALL
SELECT 2016, 9, '7', 'RVX', 9311442 ,3352 ,616.9 ,235.32
and output would be
Thanks a ton.