0

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
8

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.