Haminteu - 1 year ago 97
SQL Question

# Get last 2 years value and oldest year 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        8       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). I want to have the following result:

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

For the pivot thing, I did the following code but don't know for the calculation:

``````declare @inspectyear 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,'')

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

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

From the table above,
`Calc1`
is value on 2015 minus value on 2013.
`Calc2`
is value on 2015 minus value on 2009.

The formula above will change when new records created on 2017. So the formula for Calc1 is always get the last 2 values on years. Calc2 will always get the newest value minus the oldest value.

Does anyone have an idea for this ?

Thank you.

You want to add in these 2 calculated columns as you insert into ##temp, but you need to specify what they are at the same time as you build your dynamic query. So you use the same method as you did to get the column names - you build a string.

At the top of your workings, add in another string variable, `@calc`:

``````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;
``````

Then include that in your dynamic query string as follows:

``````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(@query);
``````

This should add the extra columns to ##temp as required.

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