?
Solved

SQL syntax for recordset of 10 second time intervals with cumulative total

Posted on 2011-09-23
15
Medium Priority
?
764 Views
Last Modified: 2012-05-12
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-9-24 16:00:00 return the cumulative 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

I've asked this question before and got an almost-complete query solution.  The problem with the query is that it gives me the "amount" accumulated during each interval as opposed to the running total (cumulative total) at that point in time.  So for example at the moment it returns:
interval                            amount
2011-09-24 17:19:20      30
2011-09-24 17:19:30      20
2011-09-24 17:19:40      3
2011-09-24 17:19:50      -10

But, given the data above, I want it to return:
interval                            amount
2011-09-24 17:19:20      30
2011-09-24 17:19:30      50
2011-09-24 17:19:40      53
2011-09-24 17:19:50      43

Here's the "almost-perfect" query:
-- if the column name is 'timestamp'

select convert(varchar(19),dateadd( ss, interval*10, CONVERT(int,getdate()) ), 121) as interval, amount
from (
 select DATEDIFF(ss,convert(int,getdate()),[timestamp])/10 as interval, SUM(amount) as amount
 from MyTable
 where [timestamp] >= convert(int,getdate())  -- this will limit the data to today's date, from midnight
 group by DATEDIFF(ss,convert(int,getdate()),[timestamp])/10
)T

How could the above query be modified so that it returns the cumulative total at each point in time?

Thanks a lot!
0
Comment
Question by:Danzigger
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 4
  • 3
15 Comments
 
LVL 33

Expert Comment

by:knightEknight
ID: 36590224
First, please change this:  CONVERT(int,getdate())    ...to this:  CONVERT(date,getdate())   ... everywhere it exists in that query.  Converting to INT sometimes introduces rounding errors.
0
 

Author Comment

by:Danzigger
ID: 36590244
Ok, cool - thanks knightEknight!
0
 

Author Comment

by:Danzigger
ID: 36590273
That produces an error:
The datepart second is not supported by date function dateadd for data type date.

Thoughts?
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 33

Expert Comment

by:knightEknight
ID: 36590285
I didn't consider that ... well, converting to INT does truncate the seconds off a date, but sometimes it will round up to tomorrows date ...  in the case of getdate(), it often rounds up after 12 noon, for example.  But if you are only working with time then it might not matter here.  Oh well, sorry to clutter your question.
0
 

Author Comment

by:Danzigger
ID: 36590309
No sweat, I appreciate you looking at it.  I am probably going to end up changing the time interval from 10 seconds to more like 10 minutes anyway.  With that in mind, do you have any thoughts on how to modify your original query to get the cumulative total each time?
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 36590343
I thought about using a CTE to do this recursively, but I never found the time... speaking of which, it is 5pm on Friday here!  I'll try to look at this later but don't hold your breath!  :)
0
 

Author Comment

by:Danzigger
ID: 36590894
Hehe, fair enough - enjoy your weekend!
0
 
LVL 22

Expert Comment

by:Thomasian
ID: 36590991
Do you need to return records evey 10 seconds even when there are no changes?

e.g.

interval                            amount
2011-09-24 17:19:10      30
2011-09-24 17:19:20      30   ->If there's no data between 17:19:11 and 17:19:20, do you want to show this record?
2011-09-24 17:19:30      50
2011-09-24 17:19:40      53
2011-09-24 17:19:50      43
0
 
LVL 22

Expert Comment

by:Thomasian
ID: 36591039
;WITH CTE AS (
 select DATEDIFF(ss,convert(int,getdate()),[timestamp])/10 as interval, SUM(amount) as amount
 from MyTable
 where [timestamp] >= convert(int,getdate())  -- this will limit the data to today's date, from midnight
 group by DATEDIFF(ss,convert(int,getdate()),[timestamp])/10
)
select convert(varchar(19),dateadd( ss, interval*10, CONVERT(int,getdate()) ), 121) as interval, T2.amount
FROM CTE T1 CROSS APPLY
    (SELECT amount=SUM(amount)
     FROM CTE
     WHERE T1.interval>=interval
    ) T2
0
 

Author Comment

by:Danzigger
ID: 36591302
Thanks Thomasian - that's a good question and the answer is yes.  If there's no change I still want a record for that interval.  
0
 
LVL 22

Accepted Solution

by:
Thomasian earned 2000 total points
ID: 36591320
DECLARE @MyTable table([timestamp] datetime, amount int)

INSERT @MyTable
SELECT '2011-09-24 01:19:10', 30
UNION ALL SELECT '2011-09-24 01:19:21', 50
UNION ALL SELECT '2011-09-24 01:19:25', 50
UNION ALL SELECT '2011-09-24 01:19:37', 20
UNION ALL SELECT '2011-09-24 01:20:10', 3
UNION ALL SELECT '2011-09-24 01:19:50', -10

;WITH CTE AS (
	select DATEDIFF(ss,convert(int,getdate()),[timestamp])/10 as interval, SUM(amount) as amount
	from @MyTable
	where [timestamp] >= convert(date,getdate())  -- this will limit the data to today's date, from midnight
	group by DATEDIFF(ss,convert(int,getdate()),[timestamp])/10
), Intervals AS (
	select top 1 interval
	from CTE
	ORDER BY interval
	
	UNION ALL
	
	SELECT interval+1
	FROM Intervals
	WHERE interval<DATEDIFF(ss,convert(int,getdate()),getdate())/10
)
select convert(varchar(19),dateadd( ss, interval*10, CONVERT(int,getdate()) ), 121) as interval, T2.amount
FROM Intervals T1 CROSS APPLY
    (SELECT amount=SUM(amount)
     FROM CTE
     WHERE T1.interval>=interval
    ) T2
OPTION (MAXRECURSION 32767)

Open in new window

0
 

Author Comment

by:Danzigger
ID: 36594521
Thanks Thomasian - it doesn't seem to work for me though.  

If I run your whole query above in SQL Management Studio I get no results, and just the messges:
(6 row(s) affected)

(0 row(s) affected)

If I remove the top table declaration and insert stuff, wire it up to my own table and begin with line 11 "WITH CTE AS (", I only get one result which is a null value in both interval and amount columns.  

Can you steer me in the right direction?

I greatly appreciate your help.
0
 

Author Comment

by:Danzigger
ID: 36594536
Doh!  Sorry - I see I just needed to change the dates on the insert statements to today's date.  

Looking good - I'll have a play...
0
 

Author Comment

by:Danzigger
ID: 36594848
It's definitely looking good - works perfectly for the supplied test data.  How would you modify this to get the same kind of results for groups of records where the timestamp is some date in the past, eg. a week ago, and where you only want to look at the cumulative total within a given timeframe.

For example, the "amounts" are product sales, each sale is assigned to an employee, each employee is assigned to a cash register, each cash register is assigned to a department, each department is assigned to a promotion.  Each promotion has a start datetime and an end datetime. So how would you modify the query to get the cumulative sales total at 10 second intervals for a promotion that started at 10 Sept 2011 9:00:00 a.m. and finished at 11 Sept 2011 at 3:00:00 a.m.?

Sorry for the scope creep!!
0
 
LVL 22

Expert Comment

by:Thomasian
ID: 36595173
You can change use @StartDate and @EndDate to specify the range.
DECLARE @MyTable table([timestamp] datetime, amount int)
DECLARE @StartDate datetime
DECLARE @EndDate datetime

SET @StartDate='2011-09-10 9:00'
SET @EndDate='2011-09-11 3:00'

INSERT @MyTable
SELECT '2011-09-10 01:19:10', 30
UNION ALL SELECT '2011-09-10 09:19:21', 50
UNION ALL SELECT '2011-09-11 01:19:25', 50
UNION ALL SELECT '2011-09-11 01:39:37', 20
UNION ALL SELECT '2011-09-11 02:20:10', 3
UNION ALL SELECT '2011-09-11 03:22:50', -10

;WITH CTE AS (
	select DATEDIFF(ss,convert(int,getdate()),[timestamp])/10 as interval, SUM(amount) as amount
	from @MyTable
	where [timestamp] BETWEEN @StartDate AND @EndDate
	group by DATEDIFF(ss,convert(int,getdate()),[timestamp])/10
), Intervals AS (
	select top 1 interval
	from CTE
	ORDER BY interval
	
	UNION ALL
	
	SELECT interval+1
	FROM Intervals
	WHERE interval<DATEDIFF(ss,convert(int,getdate()),@EndDate)/10
)
select convert(varchar(19),dateadd( ss, interval*10, CONVERT(int,getdate()) ), 121) as interval, T2.amount
FROM Intervals T1 CROSS APPLY
    (SELECT amount=SUM(amount)
     FROM CTE
     WHERE T1.interval>=interval
    ) T2
OPTION (MAXRECURSION 32767)

Open in new window

0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question