Avatar of Danzigger
Danzigger
 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!
Microsoft SQL ServerMicrosoft SQL Server 2008ASP.NET

Avatar of undefined
Last Comment
deighton

8/22/2022 - Mon
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

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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!
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.