Query sum per timeframe - 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)

Query sum per timeframe

Author

Topic

felsir
Starting Member

2 Posts

Posted - 2008-11-19 : 02:54:40

Hi,
I have no idea how to do this in a query, perhaps someone can help?

I have the following data:

DateTime                  TransactionValue
2008-18-11 10:12:10.032   $2
2008-18-11 10:12:11.021   $3
2008-18-11 10:12:11.432   $1
2008-18-11 10:12:14.091   $1
2008-18-11 10:12:17.420   $2
2008-18-11 10:12:18.321   $1

I'm looking for a query to show me the sum of transactions per timeframe (like 5 seconds):

Timeframe                 TransactSum
2008-18-11 10:12:15.000   $7
2008-18-11 10:12:20.000   $3

So the first record in the resultset is the sum of all transactions within the 10-15 second timeframe (2+3+1+1) of 2008-18-11 the second record is the sum of transactions in the 15-20 timeframe (2+1).

Any idea how to perform this in SQL?

Thanks in advance!

-Frank

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-19 : 03:55:10

[code]DECLARE @Sample TABLE
(
dt DATETIME,
value MONEY
)

INSERT @Sample
SELECT '2008-11-18 10:12:10.032', 2 UNION ALL
SELECT '2008-11-18 10:12:11.021', 3 UNION ALL
SELECT '2008-11-18 10:12:11.432', 1 UNION ALL
SELECT '2008-11-18 10:12:14.091', 1 UNION ALL
SELECT '2008-11-18 10:12:17.420', 2 UNION ALL
SELECT '2008-11-18 10:12:18.321', 1

SELECT DATEADD(SECOND, DATEDIFF(SECOND, '2008-01-01', dt) / 5 * 5, '2008-01-01 00:00:05'),
SUM(value)
FROM @Sample
GROUP BY DATEADD(SECOND, DATEDIFF(SECOND, '2008-01-01', dt) / 5 * 5, '2008-01-01 00:00:05')[/code]

E 12°55'05.63"
N 56°04'39.26"

felsir
Starting Member

2 Posts

Posted - 2008-11-19 : 04:26:15

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources