How to subtract value from same column, same table?

Question

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 11-Nov-19 14:45pm

Pedro Osvaldo

Updated 12-Nov-19 1:08am

Herman<T>.Instance

v2

Add a Solution

Comments

Tomas Takac 12-Nov-19 2:56am

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

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Answer 1 · 2019-11-11T21:01:00

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.

Posted 11-Nov-19 21:01pm

Maciej Los

v2

MadMyche · Answer 2 · 2019-11-12T01:09:00

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