Haminteu - 1 year ago 65
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.

Answer Source

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`

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download