T-SQL. How to select Item [i] and Item [i-1] on the same line WITHOUT cursor

Question

I have a table without an identifier field. structure about:

Date date,
SomeFK int,
Key float,
Value float

I need to be able to choose not to use a cursor something like

select Key, PrevKey, Value from ... where Key > @a and Key <= @b

where PrevKey should be the key from the previous record, given that the record set ordered by Key

If such data is as follows:

0.1, 2.0
0.2, 3.0
0.3, 5.0
0.4, 4.0

Then the expected result for @a = 0.15 and @b = 0.3

0.2, 0.1, 3.0
0.3, 0.2, 5.0

What I'm trying to do is calculate the formula: SUM (Value [i] * (Key [i] - Key [i-1]))

Answer 1

One way you could try is the CTE approach:

DECLARE @Data TABLE ([Key] FLOAT PRIMARY KEY, Value FLOAT)
INSERT @Data VALUES(0.1, 2.0),(0.2, 3.0), (0.3, 5.0), (0.4,4.0)

;WITH CTEData AS
(
SELECT ROW_NUMBER() OVER(ORDER BY [Key]) AS RowNo,
    [Key], Value
FROM @Data
)

SELECT d1.[Key], d2.[Key] AS PreviousKey, d1.Value
FROM CTEData d1
    INNER JOIN CTEData d2 ON d1.RowNo-1 = d2.RowNo

Edit , "", .