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

Im trying to calc the average, between a specific date/time, of the difference between the current field and the previous field. At present ive got a query which displays this but im 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 off)

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 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.

-
Thanks you very much for your help, much appreciated! – Hans Rudel Nov 5 '12 at 16:52