Solved

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

Posted on 2011-09-23
15
741 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
  • 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
 
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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 500 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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

744 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now