Group by variable time? - 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)

Group by variable time?

Author

Topic

blackjackIT
Starting Member

25 Posts

Posted - 2007-07-12 : 10:52:30

I have a table of database events. A variable number of these events can translate into a single event on the front-end. (example: user clicks a button and 150 stored procedures run). There is no identifier in the table linking the multiple db events to the single front-end events. Although, each session on the front-end does generate a new SPID.

Each db event in the table has a start and end time. I was thinking that I could group the events by time. I would specify an acceptable delta between the end of one event and the beginning of another, say 5 milliseconds, within a single SPID and group them that way. Is this possible without a cursor?

Any help is appreciated. Let me know if you need more details.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-12 : 10:57:36

something like:
group by datediff(ms, intervalStart, intervalEnd)
having datediff(ms, intervalStart, intervalEnd) = 5

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

blackjackIT
Starting Member

25 Posts

Posted - 2007-07-12 : 11:25:27

if it was a snake...

The only problem is that I'm trying to compare the interval between events, not the duration of the event itself. intervalStart and intervalEnd are in 2 different records for my comparison.

blackjackIT
Starting Member

25 Posts

Posted - 2007-07-12 : 15:26:56

I got bored and wrote the cursor


DECLARE  @events  TABLE(
                        eventid   INT,
                        rownumber INT
                        )

DECLARE  @eventid    INT,
         @rownumber  INT,
         @starttime  DATETIME,
         @endtime    DATETIME,
         @oldendtime DATETIME

SET @eventid = 1

DECLARE time_cursor CURSOR  FOR
SELECT rownumber,
       starttime,
       endtime
FROM   sqltrace

OPEN time_cursor

FETCH NEXT FROM time_cursor
INTO @rownumber,
     @starttime,
     @endtime

WHILE @@FETCH_STATUS = 0
  BEGIN
    IF Datediff(s,@oldendtime,@starttime) > 1
      SET @eventid = @eventid + 1
    
    INSERT INTO @events
    VALUES     (@eventid,
                @rownumber)
    
    SET @oldendtime = @endtime
    
    FETCH NEXT FROM time_cursor
    INTO @rownumber,
         @starttime,
         @endtime
  END

CLOSE time_cursor

DEALLOCATE time_cursor

SELECT   eventid,
         SUM(duration) / 1000000. AS duration
FROM     @events e
         JOIN sqltrace s WITH (nolock)
           ON e.rownumber = s.rownumber
GROUP BY eventid
ORDER BY duration DESC

If anyone else gets bored, show me another way

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-12 : 18:11:53

Post your table structure, sample data and expected result.

KH
[spoiler]Time is always against us[/spoiler]

blackjackIT
Starting Member

25 Posts

Posted - 2007-07-13 : 09:52:17

here's the basic table with some sample data. As you can see by my previous code, I want a sum of the duration based on an event grouping. An event is defined as any number of rows where the difference between the starttime of the current row and the endtime of the previous row is less than a specified value (originally posted as 5 milliseconds but changed to 1 second in my previous code). The cursor I wrote does exactly what i need it to do, I'm just wondering if there's a way to do it without a cursor and if it's even worth trying to code. Thanks for the feedback everyone.


create table sqltrace2(
rownumber int identity primary key,
duration bigint,
starttime datetime,
endtime datetime)

insert into sqltrace2 values(31039,'2007-07-12 08:32:49.827','2007-07-12 08:32:49.860')
insert into sqltrace2 values(30617,'2007-07-12 08:32:50.017','2007-07-12 08:32:50.047')
insert into sqltrace2 values(151,'2007-07-12 08:32:51.610','2007-07-12 08:32:51.610')
insert into sqltrace2 values(200,'2007-07-12 08:33:20.610','2007-07-12 08:33:20.610')
insert into sqltrace2 values(18522,'2007-07-12 08:33:20.640','2007-07-12 08:33:20.673')
insert into sqltrace2 values(94873,'2007-07-12 08:33:20.673','2007-07-12 08:33:20.767')
insert into sqltrace2 values(24081,'2007-07-12 08:33:20.877','2007-07-12 08:33:20.893')
insert into sqltrace2 values(67860,'2007-07-12 08:33:20.893','2007-07-12 08:33:20.970')
insert into sqltrace2 values(535,'2007-07-12 08:33:20.987','2007-07-12 08:33:20.987')
insert into sqltrace2 values(650,'2007-07-12 08:33:20.987','2007-07-12 08:33:20.987')
insert into sqltrace2 values(192,'2007-07-12 08:56:23.040','2007-07-12 08:56:23.040')
insert into sqltrace2 values(200,'2007-07-12 08:56:23.040','2007-07-12 08:56:23.040')
insert into sqltrace2 values(192,'2007-07-12 08:56:23.040','2007-07-12 08:56:23.040')
insert into sqltrace2 values(240,'2007-07-12 08:56:23.040','2007-07-12 08:56:23.040')
insert into sqltrace2 values(157,'2007-07-12 08:56:23.040','2007-07-12 08:56:23.040')
insert into sqltrace2 values(157,'2007-07-12 08:56:23.040','2007-07-12 08:56:23.040')
insert into sqltrace2 values(156,'2007-07-12 08:56:23.040','2007-07-12 08:56:23.040')
insert into sqltrace2 values(159,'2007-07-12 08:56:23.040','2007-07-12 08:56:23.040')
insert into sqltrace2 values(157,'2007-07-12 08:56:23.040','2007-07-12 08:56:23.040')
insert into sqltrace2 values(200,'2007-07-12 08:56:23.040','2007-07-12 08:56:23.040')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources