Get last 2 years value and oldest year SQL

Question

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 InspectYeardata 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.

Pl check with this http://stackoverflow.com/questions/10428993/understanding-pivot-function-in-t-sql — yuvi, Oct 21 '16 at 06:53
@yuvi, thanks for the link. But for the pivot things, I got it. What I ask is how can I get the values from the 2 latest years and the oldest value. So I can make a calculation before the table pivoted. Because the pivot would be dynamic. — Haminteu, Oct 21 '16 at 06:56
@BeanFrog, I did the pivot things, then I confuse how to make a calculation on dynamic pivot. That's why probably I have to make a calculation before the table is pivoted. — Haminteu, Oct 21 '16 at 07:07
Please post the code you have done for the "pivot things", so we can possibly use it as a starting point. — BeanFrog, Oct 21 '16 at 07:08
@BeanFrog, check the question, i add the pivot code without calculation. — Haminteu, Oct 21 '16 at 07:15

score 1 · Accepted Answer · answered Oct 21 '16 at 07:32

1

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.

answered Oct 21 '16 at 07:32

BeanFrog

2,297
12
26

Incorrect syntax near the keyword 'into'; Did I type it wrong? or? – Haminteu Oct 21 '16 at 07:54
Hmmm, I copied and pasted from my query page that worked, so I'm not sure why it wouldn't work for you. Change the final exec to print, and this will print out the query that is failing. Then paste the result, and we'll see if we can see where the issue is. – BeanFrog Oct 21 '16 at 07:57
I delete the ' + @calc + ' select into, it works. Not work when I add the calc – Haminteu Oct 21 '16 at 08:03
What you get when you change exec to print? I need to see the query that is failing to know what's gone wrong - which means I need to see what that string is. – BeanFrog Oct 21 '16 at 08:07
check my question, I change the execution way. When I print the Query. It returns a code start from ;with data as until order by Part. On the select into, it returns like select *, [2015] - [2013] as Calc1, [2015] - [2009] as Calc2, into ##temp – Haminteu Oct 21 '16 at 08:10
You need to post what the result of the print is, or I cannot help you. This is my final request before I go and do something else. – BeanFrog Oct 21 '16 at 08:12
1

Got it mate, there's an extra comma on my code. Your code works fine. You are the champion!!! – Haminteu Oct 21 '16 at 08:14

Get last 2 years value and oldest year SQL

1 Answers1