Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
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
add comment

1 Answer 1

up vote 8 down vote accepted

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.

share|improve this answer
    
Thanks you very much for your help, much appreciated! –  Hans Rudel Nov 5 '12 at 16:52
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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