15,042,397 members
See more:
I have this query
SQL
```SELECT  I.ELEMENTO , I.VALOR
FROM Operacion.Informe AS I
WHERE I.Proceso = 'IM'
AND I.NODO = 05
AND I.VERSION = 0
AND I.ANO =2019 AND I.MES=09
AND I.ITEM=2
AND CONCEPTO='COSTOREAL'
ORDER BY YEAR(I.ELEMENTO), MONTH(I.ELEMENTO)```

```ELEMENTO                                           VALOR
-------------------------------------------------- ----------------------
September 2018                                     5466.849222
October 2018                                       5231.50114
November 2018                                      5572.84702
December 2018                                      4784.915827
January 2019                                       4191.786705
February 2019                                      4369.142868
March 2019                                         5409.108792
April 2019                                         4837.711994
May 2019                                           5057.115781
June 2019                                          4890.99468
July 2019                                          4515.614788
August 2019                                        4587.52694
September 2019                                     4480.472734```

What I have tried:

```I need to subtract the first record from the last record of the query
For example the value of September 2018 subtract the value of September 2019```
Posted
Updated 12-Nov-19 1:08am
v2
Tomas Takac 12-Nov-19 2:56am

ELEMENTO is datetime? Also please update your question with the expected output.

## Solution 1

All you have to do is to get MAX[^] and MIN[^] value of `ELEMENTO` than get corresponding data for `VALOR` field and subtract them.
v2

## Solution 2

One option to compare the table values to other versions of itself is by JOINing the table to itself as the following shows.
Using the LEFT JOIN method allows the possibility to review back to the earliest records, and using the IsNull operator will compensate for the records that do not exist.
SQL
```SELECT  i.ELEMENTO, i.VALOR
,       ValorPrior = IsNull(j.Valor, 0)           -- Prior years value
,       ValorChange= i.Valor - IsNull(j.Valor, 0) -- Difference between the 2
FROM       Operacion.Informe AS i
LEFT JOIN  Operacion.Informe AS j ON Year(i.ELEMENTO) = 1 + Year(j.ELEMENTO)```
WHERE clauses have been removed so that you can review how this is working, and then you can add them back in as needed

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Top Experts
Last 24hrsThis month
 OriginalGriff 375 Richard MacCutchan 254 Richard Deeming 123 Joe Woodbury 60 k5054 50
 OriginalGriff 3,963 Richard MacCutchan 2,234 Richard Deeming 2,022 CPallini 1,293 Patrice T 874

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900