asked on

SQL syntax for recordset of 10 second time intervals

Hi

I've got a SQL Server 2008 table that records an amount (int) and a timestamp for each record. For reporting purposes I want a query that will return a recordset of the total amount at specified time intervals.

For example, starting at 2011-8-9 16:00:00 return the total amount at intervals of 10 seconds through til the current time. Which would produce a recordset like:
48
54
71
90
85 etc etc.

Or in pseudo-SQL, I want something like:
SELECT SUM(amount) FROM mytable WHERE timestamp >= '2011-8-9 9:00:00' AND timestamp <= GETDATE() ... AT INTERVALS OF 10 SECONDS

Can someone tell me what the correct SQL would be for that?

Thanks in advance!

deighton

to get the time periodsyou can use a CTE such as

declare @START datetime;
SET @START = '2011-8-9 9:00:00';

WITH CTE AS (
	SELECT @START AS Period
	UNION ALL
	SELECT DATEADD(minute,10,Period) FROM CTE WHERE DATEADD(minute,10,period) < getdate()	)
select * from CTE
OPtion(maxrecursion 0)

Open in new window

then extend this to

declare @START datetime;
SET @START = '2011-8-9 9:00:00';

WITH CTE AS (
	SELECT @START AS Period
	UNION ALL
	SELECT DATEADD(minute,10,Period) FROM CTE WHERE DATEADD(minute,10,period) < getdate()	)
	
	select C.period, sum(amount) from cte C JOIN mytable M ON M.timestamp>= c.period and M.timestamp < DATEADD(minute, 10,c.period)
	GROUP BY PERIOD
	
	OPtion(maxrecursion 0)

Open in new window

SOLUTION

knightEknight

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Danzigger

ASKER

Thanks a lot guys, I'll have a go with those and let you know how I get on.

Danzigger

ASKER

knightEknight, I've had some success with your solution thanks. The only problem is that it gives me the "amount" accumulated during that interval as opposed to the running total at that point in time. So for example at the moment it returns:
interval        amount
2011-08-10 17:19:20      30
2011-08-10 17:19:30      20
2011-08-10 17:19:40      3
2011-08-10 17:19:50      -10

Given the data above, how would I modify your suggestion so that it would return:
interval        amount
2011-08-10 17:19:20      30
2011-08-10 17:19:30      50
2011-08-10 17:19:40      53
2011-08-10 17:19:50      43

Many thanks!

ASKER CERTIFIED SOLUTION

deighton

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial