Joining an event from the same table

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 2008 Forums

Transact-SQL (2008)

Joining an event from the same table

Author

Topic

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2010-08-03 : 01:07:52

This will give my all of my events.

SELECT E.EventID, E.Cause, E.LocalStartTime, E.SessionID, E.SiteID, E.SessionStartTime, E.SourceType, E.SourceID, E.SessionSessionID, PARSession, S.EventID, s.SiteID, s.SessionID, S.LocalStartTime As SiteSelectTime, S.Cause
FROM (SELECT EventID, Cause, LocalStartTime, EventDetails.SessionID, SiteID, SessionDetails.SessionStartTime, SessionDetails.SourceType, SessionDetails.SourceID, SessionDetails.SessionID As SessionSessionID, ROW_NUMBER() Over (Partition by EventDetails.SessionID Order by EventDetails.LocalStartTime) AS PARSession
FROM EventDetails INNER JOIN
SessionDetails ON EventDetails.SessionID = SessionDetails.SessionID
Where (SessionDetails.SourceType = 'SystemMonitor' or SessionDetails.SourceType = 'VSK_AlarmServer')
) As E

This will give me all of the site select events.

SELECT EventID, Cause, LocalStartTime, EventDetails.SessionID, SiteID, SessionDetails.SessionStartTime, SessionDetails.SourceType, SessionDetails.SourceID, SessionDetails.SessionID As SessionSessionID
FROM EventDetails INNER JOIN
SessionDetails ON EventDetails.SessionID = SessionDetails.SessionID
Where SessionDetails.SourceType = 'User' AND
Cause = 'SITE SELECT'

Now what I am trying to do is join one site select event (the earliest one) to each event.

SELECT E.EventID, E.Cause, E.LocalStartTime, E.SessionID, E.SiteID, E.SessionStartTime, E.SourceType, E.SourceID, E.SessionSessionID, PARSession, S.EventID, s.SiteID, s.SessionID, S.LocalStartTime As SiteSelectTime, S.Cause
FROM (SELECT EventID, Cause, LocalStartTime, EventDetails.SessionID, SiteID, SessionDetails.SessionStartTime, SessionDetails.SourceType, SessionDetails.SourceID, SessionDetails.SessionID As SessionSessionID, ROW_NUMBER() Over (Partition by EventDetails.SessionID Order by EventDetails.LocalStartTime) AS PARSession
FROM EventDetails INNER JOIN
SessionDetails ON EventDetails.SessionID = SessionDetails.SessionID
Where (SessionDetails.SourceType = 'SystemMonitor' or SessionDetails.SourceType = 'VSK_AlarmServer')
) As E
INNER JOIN
(SELECT EventID, Cause, LocalStartTime, EventDetails.SessionID, SiteID, SessionDetails.SessionStartTime, SessionDetails.SourceType, SessionDetails.SourceID, SessionDetails.SessionID As SessionSessionID
FROM EventDetails INNER JOIN
SessionDetails ON EventDetails.SessionID = SessionDetails.SessionID
Where SessionDetails.SourceType = 'User' AND
Cause = 'SITE SELECT'
) As S ON S.LocalStartTime > E.LocalStartTime AND S.SiteID = E.SiteID
WHERE E.PARSession = 1

The code above gives the following sample result but what I really want is only rows 1, 3 and 24 where for each event it has the site select that has the earliest SiteSelectTime.

EventID Cause LocalStartTime SessionID SiteID SessionStartTime SourceType SourceID SessionSessionID PARSession EventID SiteID SessionID SiteSelectTime Cause
3807 System Monitor Error 2010-07-29 13:10:12.637 2 1 2010-07-29 13:07:01.297 SystemMonitor 1 2 1 5266 1 23 2010-07-29 13:10:50.317 SITE SELECT
3807 System Monitor Error 2010-07-29 13:10:12.637 2 1 2010-07-29 13:07:01.297 SystemMonitor 1 2 1 10399 1 2623 2010-07-30 10:37:53.093 SITE SELECT
1 CAM04 EVENT 2010-07-29 13:07:06.013 3 6 2010-07-29 13:07:05.670 VSK_AlarmServer 6 3 1 1543 6 6 2010-07-29 13:07:26.330 SITE SELECT
1 CAM04 EVENT 2010-07-29 13:07:06.013 3 6 2010-07-29 13:07:05.670 VSK_AlarmServer 6 3 1 5285 6 23 2010-07-29 13:11:21.207 SITE SELECT
1 CAM04 EVENT 2010-07-29 13:07:06.013 3 6 2010-07-29 13:07:05.670 VSK_AlarmServer 6 3 1 5305 6 44 2010-07-29 13:15:52.717 SITE SELECT
1 CAM04 EVENT 2010-07-29 13:07:06.013 3 6 2010-07-29 13:07:05.670 VSK_AlarmServer 6 3 1 5314 6 44 2010-07-29 13:17:24.283 SITE SELECT
1 CAM04 EVENT 2010-07-29 13:07:06.013 3 6 2010-07-29 13:07:05.670 VSK_AlarmServer 6 3 1 5346 6 81 2010-07-29 13:22:18.933 SITE SELECT
1 CAM04 EVENT 2010-07-29 13:07:06.013 3 6 2010-07-29 13:07:05.670 VSK_AlarmServer 6 3 1 5376 6 88 2010-07-29 13:24:28.873 SITE SELECT
1 CAM04 EVENT 2010-07-29 13:07:06.013 3 6 2010-07-29 13:07:05.670 VSK_AlarmServer 6 3 1 28105 6 4284 2010-08-02 12:21:23.793 SITE SELECT
1 CAM04 EVENT 2010-07-29 13:07:06.013 3 6 2010-07-29 13:07:05.670 VSK_AlarmServer 6 3 1 30647 6 4284 2010-08-03 10:24:34.667 SITE SELECT
1 CAM04 EVENT 2010-07-29 13:07:06.013 3 6 2010-07-29 13:07:05.670 VSK_AlarmServer 6 3 1 30740 6 4284 2010-08-03 10:56:45.067 SITE SELECT
1 CAM04 EVENT 2010-07-29 13:07:06.013 3 6 2010-07-29 13:07:05.670 VSK_AlarmServer 6 3 1 10402 6 2623 2010-07-30 10:38:00.250 SITE SELECT
1 CAM04 EVENT 2010-07-29 13:07:06.013 3 6 2010-07-29 13:07:05.670 VSK_AlarmServer 6 3 1 10808 6 2623 2010-07-30 11:29:17.777 SITE SELECT
1 CAM04 EVENT 2010-07-29 13:07:06.013 3 6 2010-07-29 13:07:05.670 VSK_AlarmServer 6 3 1 12329 6 3096 2010-07-30 13:22:52.777 SITE SELECT
1 CAM04 EVENT 2010-07-29 13:07:06.013 3 6 2010-07-29 13:07:05.670 VSK_AlarmServer 6 3 1 12762 6 4284 2010-07-30 16:25:17.910 SITE SELECT
1 CAM04 EVENT 2010-07-29 13:07:06.013 3 6 2010-07-29 13:07:05.670 VSK_AlarmServer 6 3 1 12806 6 4284 2010-07-30 16:32:12.257 SITE SELECT
1 CAM04 EVENT 2010-07-29 13:07:06.013 3 6 2010-07-29 13:07:05.670 VSK_AlarmServer 6 3 1 28061 6 4284 2010-08-02 12:19:46.330 SITE SELECT
1 CAM04 EVENT 2010-07-29 13:07:06.013 3 6 2010-07-29 13:07:05.670 VSK_AlarmServer 6 3 1 5441 6 88 2010-07-29 13:27:24.497 SITE SELECT
1 CAM04 EVENT 2010-07-29 13:07:06.013 3 6 2010-07-29 13:07:05.670 VSK_AlarmServer 6 3 1 6038 6 88 2010-07-29 14:59:06.313 SITE SELECT
1 CAM04 EVENT 2010-07-29 13:07:06.013 3 6 2010-07-29 13:07:05.670 VSK_AlarmServer 6 3 1 6073 6 88 2010-07-29 15:00:13.620 SITE SELECT
1 CAM04 EVENT 2010-07-29 13:07:06.013 3 6 2010-07-29 13:07:05.670 VSK_AlarmServer 6 3 1 6396 6 88 2010-07-29 17:02:01.777 SITE SELECT
1 CAM04 EVENT 2010-07-29 13:07:06.013 3 6 2010-07-29 13:07:05.670 VSK_AlarmServer 6 3 1 6432 6 88 2010-07-29 17:05:26.870 SITE SELECT
1 CAM04 EVENT 2010-07-29 13:07:06.013 3 6 2010-07-29 13:07:05.670 VSK_AlarmServer 6 3 1 6475 6 88 2010-07-29 17:10:46.547 SITE SELECT
3797 CAM01 EVENT 2010-07-29 13:07:49.277 7 6 2010-07-29 13:07:39.183 VSK_AlarmServer 6 7 1 5285 6 23 2010-07-29 13:11:21.207 SITE SELECT
3797 CAM01 EVENT 2010-07-29 13:07:49.277 7 6 2010-07-29 13:07:39.183 VSK_AlarmServer 6 7 1 5305 6 44 2010-07-29 13:15:52.717 SITE SELECT
3797 CAM01 EVENT 2010-07-29 13:07:49.277 7 6 2010-07-29 13:07:39.183 VSK_AlarmServer 6 7 1 5314 6 44 2010-07-29 13:17:24.283 SITE SELECT
3797 CAM01 EVENT 2010-07-29 13:07:49.277 7 6 2010-07-29 13:07:39.183 VSK_AlarmServer 6 7 1 5346 6 81 2010-07-29 13:22:18.933 SITE SELECT
3797 CAM01 EVENT 2010-07-29 13:07:49.277 7 6 2010-07-29 13:07:39.183 VSK_AlarmServer 6 7 1 5376 6 88 2010-07-29 13:24:28.873 SITE SELECT
3797 CAM01 EVENT 2010-07-29 13:07:49.277 7 6 2010-07-29 13:07:39.183 VSK_AlarmServer 6 7 1 28105 6 4284 2010-08-02 12:21:23.793 SITE SELECT
3797 CAM01 EVENT 2010-07-29 13:07:49.277 7 6 2010-07-29 13:07:39.183 VSK_AlarmServer 6 7 1 30647 6 4284 2010-08-03 10:24:34.667 SITE SELECT
3797 CAM01 EVENT 2010-07-29 13:07:49.277 7 6 2010-07-29 13:07:39.183 VSK_AlarmServer 6 7 1 30740 6 4284 2010-08-03 10:56:45.067 SITE SELECT
3797 CAM01 EVENT 2010-07-29 13:07:49.277 7 6 2010-07-29 13:07:39.183 VSK_AlarmServer 6 7 1 10402 6 2623 2010-07-30 10:38:00.250 SITE SELECT
3797 CAM01 EVENT 2010-07-29 13:07:49.277 7 6 2010-07-29 13:07:39.183 VSK_AlarmServer 6 7 1 10808 6 2623 2010-07-30 11:29:17.777 SITE SELECT
3797 CAM01 EVENT 2010-07-29 13:07:49.277 7 6 2010-07-29 13:07:39.183 VSK_AlarmServer 6 7 1 12329 6 3096 2010-07-30 13:22:52.777 SITE SELECT
3797 CAM01 EVENT 2010-07-29 13:07:49.277 7 6 2010-07-29 13:07:39.183 VSK_AlarmServer 6 7 1 12762 6 4284 2010-07-30 16:25:17.910 SITE SELECT
3797 CAM01 EVENT 2010-07-29 13:07:49.277 7 6 2010-07-29 13:07:39.183 VSK_AlarmServer 6 7 1 12806 6 4284 2010-07-30 16:32:12.257 SITE SELECT
3797 CAM01 EVENT 2010-07-29 13:07:49.277 7 6 2010-07-29 13:07:39.183 VSK_AlarmServer 6 7 1 28061 6 4284 2010-08-02 12:19:46.330 SITE SELECT
3797 CAM01 EVENT 2010-07-29 13:07:49.277 7 6 2010-07-29 13:07:39.183 VSK_AlarmServer 6 7 1 5441 6 88 2010-07-29 13:27:24.497 SITE SELECT
3797 CAM01 EVENT 2010-07-29 13:07:49.277 7 6 2010-07-29 13:07:39.183 VSK_AlarmServer 6 7 1 6038 6 88 2010-07-29 14:59:06.313 SITE SELECT
3797 CAM01 EVENT 2010-07-29 13:07:49.277 7 6 2010-07-29 13:07:39.183 VSK_AlarmServer 6 7 1 6073 6 88 2010-07-29 15:00:13.620 SITE SELECT
3797 CAM01 EVENT 2010-07-29 13:07:49.277 7 6 2010-07-29 13:07:39.183 VSK_AlarmServer 6 7 1 6396 6 88 2010-07-29 17:02:01.777 SITE SELECT
3797 CAM01 EVENT 2010-07-29 13:07:49.277 7 6 2010-07-29 13:07:39.183 VSK_AlarmServer 6 7 1 6432 6 88 2010-07-29 17:05:26.870 SITE SELECT
3797 CAM01 EVENT 2010-07-29 13:07:49.277 7 6 2010-07-29 13:07:39.183 VSK_AlarmServer 6 7 1 6475 6 88 2010-07-29 17:10:46.547 SITE SELECT

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-03 : 01:36:48

May be this?


SELECT E.EventID, E.Cause, E.LocalStartTime, E.SessionID, E.SiteID, E.SessionStartTime, E.SourceType, E.SourceID, E.SessionSessionID, PARSession, S.EventID, s.SiteID, s.SessionID, S.LocalStartTime As SiteSelectTime, S.Cause
FROM (SELECT EventID, Cause, LocalStartTime, EventDetails.SessionID, SiteID, SessionDetails.SessionStartTime, SessionDetails.SourceType, SessionDetails.SourceID, SessionDetails.SessionID As SessionSessionID, ROW_NUMBER() Over (Partition by EventDetails.SessionID Order by EventDetails.LocalStartTime) AS PARSession
FROM EventDetails INNER JOIN
SessionDetails ON EventDetails.SessionID = SessionDetails.SessionID
Where (SessionDetails.SourceType = 'SystemMonitor' or SessionDetails.SourceType = 'VSK_AlarmServer')
) As E
CROSS APPLY
(SELECT TOP 1 EventID, Cause, LocalStartTime, EventDetails.SessionID, SiteID, SessionDetails.SessionStartTime, SessionDetails.SourceType, SessionDetails.SourceID, SessionDetails.SessionID As SessionSessionID
FROM EventDetails INNER JOIN
SessionDetails ON EventDetails.SessionID = SessionDetails.SessionID
Where SessionDetails.SourceType = 'User' AND
Cause = 'SITE SELECT'
AND LocalStartTime > E.LocalStartTime 
AND SiteID = E.SiteID
ORDER BY LocalStartTime ASC
) As S 
WHERE E.PARSession = 1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2010-08-03 : 02:00:36

Your suggestion does seem to work however I think it is slow because of the CROSS APPLY creating too many rows? I added my last apply to complete the code and it takes 2:12 to return 13108 rows. If I just run the first section of code without the two cross apply it takes under 1 seconds to return 13148 rows. Any ideas on how to speed it up? Thankyou in advance.

SELECT E.EventID, E.Cause, E.LocalStartTime, E.SessionID, E.SiteID, E.SessionStartTime, E.SourceType, E.SourceID, E.SessionSessionID, PARSession, S.EventID, S.SiteID, S.SessionID, S.LocalStartTime As SiteSelectTime, S.Cause, T.EventID, T.SiteID, T.SessionID, T.LocalStartTime As SiteAckTime, T.Cause
FROM (SELECT EventID, Cause, LocalStartTime, EventDetails.SessionID, SiteID, SessionDetails.SessionStartTime, SessionDetails.SourceType, SessionDetails.SourceID, SessionDetails.SessionID As SessionSessionID, ROW_NUMBER() Over (Partition by EventDetails.SessionID Order by EventDetails.LocalStartTime) AS PARSession
FROM EventDetails INNER JOIN
SessionDetails ON EventDetails.SessionID = SessionDetails.SessionID
Where (SessionDetails.SourceType = 'SystemMonitor' or SessionDetails.SourceType = 'VSK_AlarmServer')
) As E
CROSS APPLY
(SELECT TOP 1 EventID, Cause, LocalStartTime, EventDetails.SessionID, SiteID, SessionDetails.SessionStartTime, SessionDetails.SourceType, SessionDetails.SourceID, SessionDetails.SessionID As SessionSessionID
FROM EventDetails INNER JOIN
SessionDetails ON EventDetails.SessionID = SessionDetails.SessionID
Where SessionDetails.SourceType = 'User' AND
Cause = 'SITE SELECT'
AND LocalStartTime >= E.LocalStartTime
AND SiteID = E.SiteID
ORDER BY LocalStartTime ASC
) As S
CROSS APPLY
(SELECT TOP 1 EventID, Cause, LocalStartTime, EventDetails.SessionID, SiteID, SessionDetails.SessionStartTime, SessionDetails.SourceType, SessionDetails.SourceID, SessionDetails.SessionID As SessionSessionID
FROM EventDetails INNER JOIN
SessionDetails ON EventDetails.SessionID = SessionDetails.SessionID
Where SessionDetails.SourceType = 'User' AND
Cause = 'SITE ACKNOWLEDGEMENT'
AND LocalStartTime >= E.LocalStartTime
AND SiteID = E.SiteID
ORDER BY LocalStartTime ASC
) As T
WHERE E.PARSession = 1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-03 : 02:07:07

whay two cross apply? my suggestion has only 1?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2010-08-03 : 02:21:18

The second one I have added as you can see above. Also even with one cross apply this is going to get much worse because of (AND LocalStartTime >= E.LocalStartTime AND SiteID = E.SiteID). For each site there will be more and more Site Select events that are LocalStartTime >= E.LocalStartTime as time goes by. Once I have my events I need the next site select and site acknowledgement event for the respective site. At the momnet this is the only solution I have. Which I am greatful for but concerned over increasing poor performance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-03 : 02:29:14

then why dont you try join itself and use ROW_NUMBER() to generate sequence number and select first record using row number=1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2010-08-03 : 21:26:52

I have now tried as many combinations as I can think of (too many combinations of code to paste here) using inner join and row_number and rank in the subqueries and I cannot get it to work. I have run out of ideas:)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources