Create custom field by subtracting column values from two different rows based on multiple IDs

I have to create custom column by subtracting column values from two different rows based on multiple IDs.

Data set is like below:

------------------------------------------
Date       | Name | ID1  | ID2  | Value
------------------------------------------
2020-03-01 | ABC  | 50   | 75   | 2000
2020-03-02 | ABC  | 50   | 75   | 3000
2020-03-03 | ABC  | 50   | 75   | 3140
2020-03-01 | ABC  | 50   | 76   | 5000
2020-03-02 | ABC  | 50   | 76   | 6000
2020-03-03 | ABC  | 50   | 76   | 6145
2020-03-01 | ABC  | 50   | 77   | 5000
2020-03-02 | ABC  | 50   | 77   | 6000
2020-03-03 | ABC  | 50   | 77   | 6145

ID2 is different for each date. Now I have to display custom field like below:

---------------------------------------------------
Date       | Name | ID1  | ID2  | Value |  Custom 
--------------------------------------------------
2020-03-01 | ABC  | 50   | 75   | 2000  | 1000 (3000-2000)
2020-03-02 | ABC  | 50   | 75   | 3000  | 140  (3140 -3000)
2020-03-03 | ABC  | 50   | 75   | 3140  | Next date Value - 3140 
...

...

Repeated for other ID2 also.

Please suggest how should I proceed. I tried using Cross Join but but not giving desirable result.

Answer

Try this:

DECLARE @DataSource TABLE
(
    [Date] DATE
   ,[Name] CHAR(3)
   ,[ID1] TINYINT
   ,[ID2] TINYINT
   ,[Value] INT
);

INSERT INTO @DataSource ([Date], [Name], [ID1], [ID2], [Value])
VALUES ('2020-03-01', 'ABC ', '50', '75', '2000')
      ,('2020-03-02', 'ABC ', '50', '75', '3000')
      ,('2020-03-03', 'ABC ', '50', '75', '3140')
      ,('2020-03-01', 'ABC ', '50', '76', '5000')
      ,('2020-03-02', 'ABC ', '50', '76', '6000')
      ,('2020-03-03', 'ABC ', '50', '76', '6145')
      ,('2020-03-01', 'ABC ', '50', '77', '5000')
      ,('2020-03-02', 'ABC ', '50', '77', '6000')
      ,('2020-03-03', 'ABC ', '50', '77', '6145');

SELECT *
     ,LEAD([Value]) OVER (PARTITION BY [Name], [ID1], [ID2] ORDER BY [Date] ASC)
     ,CONCAT(LEAD([Value]) OVER (PARTITION BY [Name], [ID1], [ID2] ORDER BY [Date] ASC) - [value], ' (', LEAD([Value]) OVER (PARTITION BY [Name], [ID1], [ID2] ORDER BY [Date] ASC), ' -', [value],')')
FROM @DataSource
ORDER BY [Date]

You can add more logic in the final CONCAT in order to shape the results in different way. For example, if there is not next value to be get, you can leave the value NULL.