Haminteu Haminteu - 1 month ago 8
SQL Question

Calculate Year Distance Pivot SQL

I have the following table:


--------------------------------------------------------------
InspectYear Part Pos1 Pos2 Pos3 Pos4
--------------------------------------------------------------
2009 001 8 8 9 7
2009 002 9 7 8 6
2011 001 9 9 8 7
2011 002 7 8 6 8
2013 001 8 9 7 9
2013 002 7 7 8 8
2015 001 10 8 7 4
2015 002 7 6 9 8


The
InspectYear
data will always add every 2 years for each Part.

I want to calculate the newest value on each
pos#
column with the previous year (Calc1). Also the newest value with the oldest value (Calc2).

Then, I have the following code:


declare @inspectyear as nvarchar(max), @calc as nvarchar(max), @query as nvarchar(max);

set @inspectyear = STUFF((select distinct ',' + quotename(InspectYear) from ##t2 c
for XML path(''), type).value('.','NVARCHAR(MAX)'),1,1,'')

select @calc = ', ' + quotename(Max(InspectYear)) + ' - ' + quotename(Max(InspectYear)-2)
+ ' as Calc1, ' + quotename(Max(InspectYear)) + ' - ' + quotename(min(InspectYear))
+ ' as Calc2' from #t2;

set @query =
';with data as
(
select inspectyear,
partno, Pos, number
from #t2
unpivot
(
number
for Pos in ([Pos1], [Pos2], [Pos3], [Pos4])
) unpvt
)
select * ' + @calc + ' into ##temp
from data
pivot
(
sum(number)
for inspectyear in (' + @inspectyear + ')
) pvt
order by partno';

exec sp_executesqk @query = @query;
select * from ##temp;
drop table ##temp;


the result should be:


---------------------------------------------------------------------
Part Pos 2009 2011 2013 2015 Calc1 Calc2
---------------------------------------------------------------------
001 Pos1 8 9 8 10 2 2
001 Pos2 8 9 9 8 -1 0
001 Pos3 9 8 7 7 0 -2
001 Pos4 7 7 9 4 -5 -3


I want to modify the calculation for Calc1 and Calc2 which is:


Calc1 = (newest value on each pos# column - value on the previous year on each pos#) / distance from latest year until the previous year.

Calc2 = (newest value on each pos# column - value on the first year on each pos#) / distance from latest year until the first year.


For instance:


---------------------------------------------------------------------
Part Pos 2009 2011 2013 2015 Calc1 Calc2
---------------------------------------------------------------------
001 Pos1 8 9 8 10 1 0.333


Year distance on the table above are, for
Calc1 2015 - 2013 = 2


for
Calc2 2015 - 2009 = 6



So the question is, how can I get the distance between the newest year minus previous year and the newest year minus the oldest year...?


Does anyone have an idea for this?

Thank you.

ZLK ZLK
Answer

As mentioned in the comments, the easy fix here is to change the @calc variable to look like this:

select @calc = ', (' + quotename(Max(InspectYear)) + ' - ' + quotename(Max(InspectYear)-2) 
         + ') / 2.0 as Calc1, 1.0 * (' + quotename(Max(InspectYear)) + ' - ' + quotename(min(InspectYear)) 
         + ') / (' + cast(max(inspectyear) as char(4)) + '-' + cast(min(inspectyear) as char(4)) + ') as Calc2' 
from #t2;

Which will give you output similar to this: , ([2015] - [2013]) / 2.0 as Calc1, 1.0 * ([2015] - [2009]) / (2015-2009) as Calc2