Computation between two rows of same table

Transact-SQL
1

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.

2

Using your formulars, I get different results.

Regarding SGR you state that:

  • SGR = NPS(2014)-NPS(2016/NPS(2014))
  • SGR = 3456.05-(3603.33/3456.05)
  • if my calculator is correct, the result is 3455.007 (not 0.043 as you stated)

Regarding PGR you state that:

  • PGR = CBP(2014)-CBP(2016/CBP(2014))
  • PGR = 1059.24-(1053.07/1059.24)
  • if my calculator is correct, the result is 1058.246 (not 0.006 as you stated)

Please specify in details, how you arrive at your results.

3

Hello Bitsmed
Thank You Very much for response. i have edited the question to have more clear picture.

Please help.

Regards

4

Maybe something like:

select a.*
      ,case
          when b.[YEAR] is null
           and a.[YEAR]=year(getdate())
          then 1.0
          when b.[YEAR] is null
          then 100.0
          else (b.SALES-a.SALES)/b.SALES
       end as Sales_growth
      ,case
          when b.[YEAR] is null
           and a.[YEAR]=year(getdate())
          then 1.0
          when b.[YEAR] is null
          then 100.0
          else (b.Margin-a.Margin)/b.Margin
       end as margin_rate
  from @T1 as a
       left outer join @T1 as b
                    on b.[Month]=a.[Month]
                   and b.SM=a.SM
                   and b.SG=a.SG
                   and b.CUST=a.CUST
                   and b.SP=a.SP
                   and b.[Year]=a.[Year]-1
1 Like
5

Thank You Once again Bitsmed.