Click here to Skip to main content
15,042,397 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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
Comments
Tomas Takac 12-Nov-19 2:56am
   
ELEMENTO is datetime? Also please update your question with the expected output.

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
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)




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