Get sum for time interval - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Get sum for time interval

Author

Topic

jh_sql
Starting Member

24 Posts

Posted - 2013-08-28 : 07:17:46

Hello i have table, lets say:

DECLARE @Order TABLE (
amount INT, start_time datetime, end_time datetime)
insert into @Order
SELECT 2, '1/1/2013 10:30', '1/1/2013 11:30' union all
SELECT 1, '1/1/2013 11:15', '1/1/2013 12:30' union all
SELECT 1, '1/1/2013 12:15', '1/1/2013 12:30'

So the data is:
amount start_time end_time
2 2013-01-01 10:30:00.000 2013-01-01 11:30:00.000
1 2013-01-01 11:15:00.000 2013-01-01 12:30:00.000
1 2013-01-01 12:15:00.000 2013-01-01 12:30:00.000

And i need to get sum of the amount column for 15 minutes time intervals, so the result would be:
amount start_time end_time
0 2013-01-01 10:00 2013-01-01 10:15
2 2013-01-01 10:15 2013-01-01 10:30
2 2013-01-01 10:30 2013-01-01 10:45
2 2013-01-01 10:45 2013-01-01 11:00
3 2013-01-01 11:00 2013-01-01 11:15
3 2013-01-01 11:15 2013-01-01 11:30
3 2013-01-01 11:30 2013-01-01 11:45
2 2013-01-01 11:45 2013-01-01 12:00
.
.
.

Can anyone help me achieving this, i know i could get it with doing multiple queries,with each interval in its own query, but can this be done with single query?

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-28 : 08:26:54

[code]DECLARE @Order TABLE (
amount INT, start_time datetime, end_time datetime)
insert into @Order
SELECT 2, '1/1/2013 10:30', '1/1/2013 11:30' union all
SELECT 1, '1/1/2013 11:15', '1/1/2013 12:30' union all
SELECT 1, '1/1/2013 12:15', '1/1/2013 12:30'

;WITH CTE(amount, StartTime, EndTime, end_time) AS
( SELECT amount, start_time, DATEADD(MI, 15, start_time) EndTime, end_time FROM @order
UNION ALL
SELECT amount, EndTime, DATEADD(MI, 15, EndTime), End_time
FROM CTE
WHERE DATEADD(MI, 15, EndTime) <= End_time
) SELECT * FROM CTE
ORDER BY StartTime[/code]

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-28 : 12:04:47

see

http://visakhm.blogspot.in/2010/02/aggregating-data-over-time-slots.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

jh_sql
Starting Member

24 Posts

Posted - 2013-08-29 : 01:24:35

Thank you!

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-29 : 02:17:34

quote:
Originally posted by jh_sql

Thank you!

Welcome

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources