regarding grouping - 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 2000 Forums

SQL Server Development (2000)

regarding grouping

Author

Topic

sathishkumar
Starting Member

21 Posts

Posted - 2001-12-16 : 23:23:28

I have a derived table with timestamp and data columns,
i want to sum the data based on the user input @aggregationinterval. i don't want to use any looping or cursor.

eg ,if the user input for @aggregationinterval is 3 mins , i want to sum records in derived table one record for every 3 mins

with regards
sathish

VyasKN
SQL Server MVP & SQLTeam MVY

313 Posts

Posted - 2001-12-16 : 23:46:33

Satish, to help us help you better, please post your table structure, some sample data and the required output. Otherwise we have to guess what you are after.

--
HTH,
Vyas
SQL Server FAQ, articles, code samples,
best practices, interview questions and much more @
http://vyaskn.tripod.com

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2001-12-16 : 23:50:01

But guessing is half the fun

Damian

sathishkumar
Starting Member

21 Posts

Posted - 2001-12-17 : 00:08:43

sorry guys,
here the table columns,

Timestamp
Packets ,
bytes

sample data
timeStamp packets bytes
12/12/01 10:00:00 AM 12 1000
12/12/01 10:00:02 AM 11 1040
12/12/01 10:00:04 AM 12 1000
12/12/01 10:00:06 AM 11 1020

Aggregation interval for sample 3 secs

i need the ouput like this, the aggregation intreval may vary, but i dont want to use any looping, i prefer grouping

timeStamp packets bytes
12/12/01 10:00:03 AM 23 2040
12/12/01 10:00:06 AM 23 2020

with regards
sathish

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2001-12-17 : 05:22:05

In this query, '2000-01-01 00:00:00' is arbitrary, but should be before any of the results so that the value fed to the mod operator stays positive. The derived table in this query isn't really necessary, it just keeps that ugly DATEADD expression in one place.


SELECT
  timeStamp,
  SUM(packets) AS packets,
  SUM(bytes) AS bytes
FROM (
  SELECT
    DATEADD(s,
      -(DATEDIFF(s, '2000-01-01 00:00:00', timeStamp) % @aggregationinterval),
      timeStamp) AS timeStamp,
    packets,
    bytes
  FROM PacketTimestamps) a
GROUP BY timeStamp
ORDER BY timeStamp

If you need to include all possible time periods in the desired range, irrespective of whether there are any rows in PacketTimestamps with such a timeStamp you will need to join a table of possible time periods. Something similar to my posting in this thread should do the trick: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=11308

Edited by - Arnold Fribble on 12/17/2001 05:33:08

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources