Solved

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

Posted on 2011-09-23
15
754 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Suggested Solutions

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

856 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