SQL difference between cell values (LAG function not working)-CodePudding

Unfortunately I can't use the lag function due to using SQL Server 2008.

I need to find machine based difference between two values at different times.

I have a table like that:

ID    |   Time    |  Value  | Machine
------ ----------- --------- -----------
7     | 12:00    | 154400   |  20
6     | 12:00    | 77840    |  17
5     | 11:00    | 10480    |  11
4     | 11:00    | 13214    |  14
3     | 11:00    | 76340    |  17
2     | 11:00    | 15410    |  20
1     | 10:00    | 75000    |  17

I need to find a difference (76340-75000 = 1340) between id 1 and 3.

Tried these before:

select 
    t1.Machine, t1.[ID], t2.[ID], t1.Value, t2.Value, 
    t2.Value - t1.Value as difference
from 
    hava t1
cross apply 
    hava t2
where 
    t1.Machine = t2.Machine 
    and t1.ID <> t2.ID 
    and t1.Machine = 17

but it compares with t1.id with the whole table.

I can't use lag function - thank you very much for your help.

CodePudding user response：

WITH CTE(ID , Time, Value, Machine)AS
(
   SELECT 7,'12:00',154400,20 UNION ALL
   SELECT 6,'12:00',77840,17 UNION ALL
   SELECT 5,'11:00',10480,11 UNION ALL
   SELECT 4,'11:00',13214,14 UNION ALL
   SELECT 3,'11:00',76340,17 UNION ALL
   SELECT 2,'11:00',15410,20 UNION ALL
   SELECT 1,'10:00',75000,17
),
CTE2 AS 
(
  SELECT C.ID,C.TIME,C.Value,C.Machine,
  ROW_NUMBER()OVER(PARTITION BY C.MACHINE ORDER BY C.TIME ASC)XCOL
   FROM CTE AS C
)
SELECT X.ID,X.Time,X.Value, X2.Value,  X.Machine
FROM CTE2 AS X
LEFT JOIN CTE2 AS X2 ON X.Machine=X2.Machine AND X.XCOL=X2.XCOL-1
WHERE X.Machine=17