2

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 >= V.DateKey)
from [dbo].[ValueTest] V
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.