Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2011-09-23
15
Medium Priority
?
766 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

618 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