Solved

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

Posted on 2011-09-23
15
758 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

726 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