# Getting every n rows from data model

• ### 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 Monday, January 20, 2014 12:07 PM
Monday, January 20, 2014 11:59 AM

• 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]
,[Bid]
,[OrderSymbol] AS Symbol
FROM [dbo].[Rates]
WHERE DATEPART(minute, [RateTime])=0```

Re

Dennis

• Marked as answer by 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
)
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]