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

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

• Could you perhaps post a load script which includes the Data table's definition and some sample data? What would also be helpful is a sample result set based on that sample data, showing exactly what you want to see. – Kevin Feasel Nov 5 '12 at 15:45

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 `pair`s 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.