SQL Query for getting the difference between a value from one row with a value another row

Question

I have data as shown in the table below

|ItemNo| Date |Value|
--------------------------
| 101 | 201002 | 5 |
| 102 | 201002 | 3 |
| 201 | 201002 | 7 |
| 202 | 201002 | 2 |

| 101 | 201003 | 6 |
| 102 | 201003 | 3 |
| 201 | 201003 | 7 |
| 202 | 201003 | 1 |

Result Expected
|ItemNo| Date |Value|
--------------------------
| 100 | 201002 | 2 |
| 200 | 201002 | 5 |
| 100 | 201003 | 3 |
| 200 | 201003 | 6 |

Sorry for the format. I have reformatted it now. I am trying to get the difference in Value between Item No 101 and 102 for the Date 201002. Is this possible to get. Any help is greatly appreciated.

Answer 1

Use a self-join:

SELECT
    T1.ItemNo - 1 AS ItemNo,
    T1.Date,
    T1.Value - T2.Value AS Value
FROM table1 T1
JOIN table1 T2
ON T1.Date = T2.Date
AND T1.ItemNo + 1 = T2.ItemNo