Query to find the subtract of each column from the following column Hi everyone hope you are doing well
   I have a table with this structure
   Table 1:

 [dbo].[ValueTest] (Date , PorojectCode , Value)


It has this data 

('1397/04/01' , 'A' , 100) , ('1397/04/03' , 'A' , 120) , ('1397/04/05' ,'A',140)


I need to have the result below :

 ProjectCode    Value
-------------   -------
    A              20
    A              20

I Mean I need to have the subtract of the value of each column from the following column
I have tried this query but I did not get the result


Thanks for helping me

Select ProjectCode , 
       (Select v.Value - v2.Value
        From [dbo].[ValueTest] V2
        Where v2.DateKey &gt;= V.DateKey)
from [dbo].[ValueTest] V
You could use LAG() to subtract the value from the previous value, ordered by DateKey.

SELECT ProjectCode, (V.value - LAG(v.value) over (order by Datekey)) as val
from [dbo].[ValueTest] V;


But this will give you an extra NULL for the first value since we are doing 100 - NULL (Non existing row since the row with val 100 is the first one).

One way around this is by removing the NULL with a CTE

;WITH CTE
AS
(
SELECT ProjectCode, (V.value - LAG(v.value) over (order by Datekey)) as val
from [dbo].[ValueTest] V
)

SELECT * FROM CTE 
where val IS NOT NULL;


Result

ProjectCode val
A   20
A   20