locked
Getting every n rows from data model RRS feed

  • Question

  • I have a table of Forex rate quotes for several symbols in a SQL Azure table that are updated every 5 minutes. When using that data in a PivotChart (Excel 2013) I can only get about 50 days worth of data before the chart rendering engine breaks down. Is it possible to send only every nth row to a PivotChart so that I can show longer periods of time for some charts?

    I would prefer not to load the data twice from the database, but if that is the only solution, the table structure is SerialStamp (bigint), RateTime (datetime), Bid (decimal), Ask (decimal), OrderSymbol (char).

    TIA!

    Dennis


    • Edited by sgude1 Monday, January 20, 2014 12:07 PM
    Monday, January 20, 2014 11:59 AM

Answers

  • It may not be as flexible, but since I run the update algorithm every 5 minutes and round the time to 0, 5, 10 minutes etc, I ended up using this simple filter, which gives me what I need.

    SELECT [RateTime]
          ,[Ask]
    	  ,[Bid]
          ,[OrderSymbol] AS Symbol
      FROM [dbo].[Rates]
      WHERE DATEPART(minute, [RateTime])=0

    Re

    Dennis

    • Marked as answer by sgude1 Monday, January 27, 2014 2:47 PM
    Monday, January 27, 2014 2:46 PM

All replies

  • Sure.  Just write a source query in TSQL to retrieve the data.

    EG here's how to retrieve every 10th row:

      with q as
      (
        select *, row_number() over (order by (select null)) rn
        from Person.Address
      )
      select *
      from q
      where rn % 10 = 1
    David


    David http://blogs.msdn.com/b/dbrowne/

    Monday, January 20, 2014 5:39 PM
  • I tried a statement very similar to that, but the problem is that I need to group on OrderSymbol too. If I had 10 different variants in OrderSymbol, using this code I would only get the rates for one symbol.

    Every 5 minutes each symbol (EUR/USD, GBP/USD etc) will insert it's current rate in a new row, so what I need is the n'th row for EUR/USD + the n'th row for GBP/USD etc. And since the amount of symbols I collect will vary over time, I can't simply use math to count either, it has to be grouped somehow.

    Re

    Dennis

    Monday, January 20, 2014 5:48 PM
  • Here, integer division is your friend. For each row compute the number of seconds since some fixed date and divide that by the number of seconds in the interval.  Division of integers in TSQL will never have a fractional component

    egm grouping AdventureWorks SalesOrderDetail by produce and 5-min date range:

    select ProductId, avg(UnitPrice) Price, max(ModifiedDate) PeriodEnd
    from Sales.SalesOrderDetail
    group by ProductId, datediff(s,'1980-01-01',ModifiedDate) / (60*5)
    order by productId, PeriodEnd

    David


    David http://blogs.msdn.com/b/dbrowne/

    Monday, January 20, 2014 7:56 PM
  • It may not be as flexible, but since I run the update algorithm every 5 minutes and round the time to 0, 5, 10 minutes etc, I ended up using this simple filter, which gives me what I need.

    SELECT [RateTime]
          ,[Ask]
    	  ,[Bid]
          ,[OrderSymbol] AS Symbol
      FROM [dbo].[Rates]
      WHERE DATEPART(minute, [RateTime])=0

    Re

    Dennis

    • Marked as answer by sgude1 Monday, January 27, 2014 2:47 PM
    Monday, January 27, 2014 2:46 PM