Average, between specific date/time, of the difference between current field and previous field

Question

I'm trying to calculate the average, between a specific date/time, of the difference between the current field and the previous field. At present I've got a query which displays this but I'm unsure how I can now filter this such that I can get the average of the 'Diff' column between a specific date/time. (Apologies in advance for my sql skill, or lack of.)

SELECT 
      S.Pair, 
      S.Date, 
      S.Time, 
      S.Bid, 
      S.Time, 
      ABS(S.Bid - T.Bid) as 'Diff'
FROM 
    dbo.Data as S
CROSS APPLY
           (
            SELECT TOP 1 T.Bid
            FROM
                dbo.Data as T
            WHERE
                 T.Id > S.Id
           ) as T

Any pointers would be appreciated.

EDIT:

http://sqlfiddle.com/#!3/d4910/1

Answer 1

I used your SQL Fiddle and posted a working solution here.

WITH Calc AS
(
    SELECT
        S.Pair,
        S.Date,
        S.Time,
        S.Bid,
        ABS(S.Bid - T.Bid) AS Diff
    FROM dbo.forexData AS S
    CROSS APPLY
    (
        SELECT TOP (1)
            T.Bid
        FROM dbo.forexData AS T
        WHERE
            T.Id > S.Id
            AND t.Pair = s.Pair
        ORDER BY
            T.Bid DESC
    ) AS T
)
SELECT
    Pair,
    AVG(DIFF)
FROM Calc
GROUP BY
    Pair;

To summarize:

• I fixed a hole in your existing logic - currently your CROSS APPLY doesn't link to the same pair value so if you have multiple pairs then your DIFF could be incorrect.
• I put the fixed logic into a CTE
• I queried the CTE with a GROUP BY on pair
• I added an ORDER BY specification to determine the TOP ordering

You can add a date filter in a WHERE to the outer SELECT if you desire.