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 events from two tables

Author  Topic 

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts
Posted - 2012-11-14 : 00:41:07
I have a source table and will be working with the following fields;

SELECT TOP 1000 [Time]
,[SiteID]
,[SiteName]
,[SiteTx]
,[Type]
,[Source]
,[Details]
,[ResponseTime]
,[DurationOnASL]
FROM [VC].[dbo].[Results]
Where TYPE = 'Finished' OR TYPE = 'Site Acknowledge'
Order By Time Asc


So the output from this table looks like this;

Time SiteID SiteName SiteTx Type Source Details ResponseTime DurationOnASL
2012-11-14 09:11:41.000 BATMAN Batman FastTrace-FT-R-FastTx Site Acknowledge User Interface 1 SJH 61695 0
2012-11-14 09:11:41.000 BATMAN Batman FastTrace-FT-R-FastTx Finished SM NULL 0 61700
2012-11-14 09:13:55.000 BATMAN Batman FastTrace-FT-R-FastTx Site Acknowledge User Interface 1 SJH 11 0
2012-11-14 09:13:55.000 BATMAN Batman FastTrace-FT-R-FastTx Finished SM NULL 0 14
2012-11-14 09:14:18.000 CHAM Chameleon (PSTN, ISDN, LAN) FastTrace-FT-R-FastTx Finished SM NULL 0 3
2012-11-14 09:16:22.000 BATMAN Batman FastTrace-FT-R-FastTx Site Acknowledge User Interface 1 SJH 13 0
2012-11-14 09:16:22.000 BATMAN Batman FastTrace-FT-R-FastTx Finished SM NULL 0 16
2012-11-14 09:16:29.000 DILBERT DILBERT FastTrace-FT-R-FastTx Site Acknowledge User Interface 1 SJH 3 0
2012-11-14 09:16:29.000 DILBERT DILBERT FastTrace-FT-R-FastTx Finished SM NULL 0 20
2012-11-14 09:18:19.000 DILBERT DILBERT FastTrace-FT-R-FastTx Site Acknowledge User Interface 1 SJH 4 0
2012-11-14 09:18:19.000 DILBERT DILBERT FastTrace-FT-R-FastTx Finished SM NULL 0 10
2012-11-14 09:20:17.000 DILBERT DILBERT FastTrace-FT-R-FastTx Site Acknowledge User Interface 1 SJH 3 0
2012-11-14 09:20:17.000 DILBERT DILBERT FastTrace-FT-R-FastTx Finished SM NULL 0 8
2012-11-14 09:22:22.000 INV_MAN Invisible Man FastTrace-FT-R-FastTx Finished SM NULL 0 86
2012-11-14 09:22:30.000 DILBERT DILBERT FastTrace-FT-R-FastTx Site Acknowledge User Interface 1 SJH 17 0
2012-11-14 09:22:30.000 DILBERT DILBERT FastTrace-FT-R-FastTx Finished SM NULL 0 21
2012-11-14 09:23:23.000 BATMAN Batman FastTrace-FT-R-FastTx Finished SM NULL 0 48
2012-11-14 09:24:15.000 BATMAN Batman FastTrace-FT-R-FastTx Site Acknowledge User Interface 1 SJH 13 0
2012-11-14 09:24:16.000 BATMAN Batman FastTrace-FT-R-FastTx Finished SM NULL 0 22
2012-11-14 09:24:21.000 DILBERT DILBERT FastTrace-FT-R-FastTx Site Acknowledge User Interface 1 SJH 8 0
2012-11-14 09:24:21.000 DILBERT DILBERT FastTrace-FT-R-FastTx Finished SM NULL 0 12
2012-11-14 09:24:45.000 INV_MAN Invisible Man FastTrace-FT-R-FastTx Site Acknowledge User Interface 1 SJH 19 0
2012-11-14 09:24:45.000 INV_MAN Invisible Man FastTrace-FT-R-FastTx Finished SM NULL 0 23
2012-11-14 09:25:18.000 BATMAN Batman FastTrace-FT-R-FastTx Site Acknowledge User Interface 1 SJH 19 0
2012-11-14 09:25:18.000 BATMAN Batman FastTrace-FT-R-FastTx Finished SM NULL 0 28
2012-11-14 09:26:17.000 DILBERT DILBERT FastTrace-FT-R-FastTx Site Acknowledge User Interface 1 SJH 3 0
2012-11-14 09:26:17.000 DILBERT DILBERT FastTrace-FT-R-FastTx Finished SM NULL 0 8
2012-11-14 09:26:31.000 INV_MAN Invisible Man FastTrace-FT-R-FastTx Site Acknowledge User Interface 1 SJH 4 0
2012-11-14 09:26:31.000 INV_MAN Invisible Man FastTrace-FT-R-FastTx Finished SM NULL 0 9
2012-11-14 09:27:26.000 BATMAN Batman FastTrace-FT-R-FastTx Site Acknowledge User Interface 1 SJH 3 0
2012-11-14 09:27:26.000 BATMAN Batman FastTrace-FT-R-FastTx Finished SM NULL 0 8
2012-11-14 09:27:33.000 INV_MAN Invisible Man FastTrace-FT-R-FastTx Site Acknowledge User Interface 1 SJH 5 0
2012-11-14 09:27:38.000 INV_MAN Invisible Man FastTrace-FT-R-FastTx Site Acknowledge User Interface 1 SJH 2 0
2012-11-14 09:27:38.000 INV_MAN Invisible Man FastTrace-FT-R-FastTx Finished SM NULL 0 14
2012-11-14 09:28:17.000 DILBERT DILBERT FastTrace-FT-R-FastTx Site Acknowledge User Interface 1 SJH 5 0
2012-11-14 09:28:17.000 DILBERT DILBERT FastTrace-FT-R-FastTx Finished SM NULL 0 8
2012-11-14 09:36:09.000 INV_MAN Invisible Man FastTrace-FT-R-FastTx Site Acknowledge User Interface 1 SJH 387 0
2012-11-14 09:36:09.000 INV_MAN Invisible Man FastTrace-FT-R-FastTx Finished SM NULL 0 391
2012-11-14 09:36:13.000 BATMAN Batman FastTrace-FT-R-FastTx Site Acknowledge User Interface 1 SJH 304 0
2012-11-14 09:36:14.000 BATMAN Batman FastTrace-FT-R-FastTx Finished SM NULL 0 307
2012-11-14 09:36:19.000 DILBERT DILBERT FastTrace-FT-R-FastTx Site Acknowledge User Interface 1 SJH 366 0
2012-11-14 09:36:19.000 DILBERT DILBERT FastTrace-FT-R-FastTx Finished SM NULL 0 370
2012-11-14 09:38:28.000 INV_MAN Invisible Man FastTrace-FT-R-FastTx Site Acknowledge User Interface 1 SJH 45 0
2012-11-14 09:38:29.000 INV_MAN Invisible Man FastTrace-FT-R-FastTx Finished SM NULL 0 49
2012-11-14 09:38:32.000 BATMAN Batman FastTrace-FT-R-FastTx Site Acknowledge User Interface 1 SJH 16 0
2012-11-14 09:38:33.000 BATMAN Batman FastTrace-FT-R-FastTx Finished SM NULL 0 19
2012-11-14 09:38:38.000 DILBERT DILBERT FastTrace-FT-R-FastTx Site Acknowledge User Interface 1 SJH 24 0
2012-11-14 09:38:38.000 DILBERT DILBERT FastTrace-FT-R-FastTx Finished SM NULL 0 29
2012-11-14 09:41:22.000 INV_MAN Invisible Man FastTrace-FT-R-FastTx Site Acknowledge User Interface 1 SJH 95 0
2012-11-14 09:41:22.000 INV_MAN Invisible Man FastTrace-FT-R-FastTx Finished SM NULL 0 104
2012-11-14 09:41:27.000 BATMAN Batman FastTrace-FT-R-FastTx Site Acknowledge User Interface 1 SJH 17 0
2012-11-14 09:41:27.000 BATMAN Batman FastTrace-FT-R-FastTx Finished SM NULL 0 21
2012-11-14 09:41:32.000 DILBERT DILBERT FastTrace-FT-R-FastTx Site Acknowledge User Interface 1 SJH 79 0
2012-11-14 09:41:32.000 DILBERT DILBERT FastTrace-FT-R-FastTx Finished SM NULL 0 83
2012-11-14 09:41:50.000 INV_MAN Invisible Man FastTrace-FT-R-FastTx Site Acknowledge User Interface 1 SJH 3 0
2012-11-14 09:41:51.000 INV_MAN Invisible Man FastTrace-FT-R-FastTx Finished SM NULL 0 12
2012-11-14 09:43:13.000 DILBERT DILBERT FastTrace-FT-R-FastTx Site Acknowledge User Interface 1 SJH 61 0
2012-11-14 09:43:14.000 DILBERT DILBERT FastTrace-FT-R-FastTx Finished SM NULL 0 64
2012-11-14 09:58:47.000 BATMAN Batman FastTrace-FT-R-FastTx Site Acknowledge User Interface 1 SJH 916 0
2012-11-14 09:58:47.000 BATMAN Batman FastTrace-FT-R-FastTx Finished SM NULL 0 920
2012-11-14 09:58:54.000 INV_MAN Invisible Man FastTrace-FT-R-FastTx Site Acknowledge User Interface 1 SJH 795 0
2012-11-14 09:58:54.000 INV_MAN Invisible Man FastTrace-FT-R-FastTx Finished SM NULL 0 799
2012-11-14 09:59:01.000 DILBERT DILBERT FastTrace-FT-R-FastTx Site Acknowledge User Interface 1 SJH 888 0
2012-11-14 09:59:01.000 DILBERT DILBERT FastTrace-FT-R-FastTx Finished SM NULL 0 892
2012-11-14 10:47:02.000 INV_MAN Invisible Man FastTrace-FT-R-FastTx Site Acknowledge User Interface 1 SJH 2844 0
2012-11-14 10:47:03.000 INV_MAN Invisible Man FastTrace-FT-R-FastTx Finished SM NULL 0 2847
2012-11-14 10:47:10.000 BATMAN Batman FastTrace-FT-R-FastTx Site Acknowledge User Interface 1 SJH 2759 0
2012-11-14 10:47:10.000 BATMAN Batman FastTrace-FT-R-FastTx Finished SM NULL 0 2763
2012-11-14 10:47:14.000 DILBERT DILBERT FastTrace-FT-R-FastTx Site Acknowledge User Interface 1 SJH 2821 0
2012-11-14 10:47:15.000 DILBERT DILBERT FastTrace-FT-R-FastTx Finished SM NULL 0 2824
2012-11-14 11:23:53.000 INV_MAN Invisible Man FastTrace-FT-R-FastTx Site Acknowledge User Interface 1 SJH 2170 0
2012-11-14 11:23:53.000 INV_MAN Invisible Man FastTrace-FT-R-FastTx Finished SM NULL 0 2178
2012-11-14 11:23:57.000 BATMAN Batman FastTrace-FT-R-FastTx Site Acknowledge User Interface 1 SJH 1968 0
2012-11-14 11:23:58.000 BATMAN Batman FastTrace-FT-R-FastTx Finished SM NULL 0 1971
2012-11-14 11:24:03.000 DILBERT DILBERT FastTrace-FT-R-FastTx Site Acknowledge User Interface 1 SJH 2150 0
2012-11-14 11:24:03.000 DILBERT DILBERT FastTrace-FT-R-FastTx Finished SM NULL 0 2154

Now I have some code that uses the results table and splits the finish rows into a temp table. After that I need to re-join the finish events to the site acknowledge events. There are also cases where there will be finish events without site acknowledge events and multiple site acknowledge events which will match to only one finish event (being the next one in time). The code I have actually gives the result I am after however it is incomplete as I will show.

CREATE TABLE #ie
(
[TimeFin] DATETIME,
[SiteIDFin] NVARCHAR(MAX),
[SiteNameFin] NVARCHAR(MAX),
[SiteTxFin] NVARCHAR(MAX),
[TypeFin] NVARCHAR(MAX),
[SourceFin] NVARCHAR(MAX),
[DetailsFin] NVARCHAR(MAX),
[ResponseTimeFin] INT,
[EventToAck] INT
)

INSERT INTO #ie
--Selects all Site Finsihed within the search range.
SELECT [Time],
[SiteID],
[SiteName],
[SiteTx],
[Type],
[Source],
[Details],
[ResponseTime],
[DurationOnASL]
FROM dbo.Results
WHERE Type = 'Finished'
AND [Time] >= @StartDateRange
AND [Time] <= @EndDateRange
ORDER BY Time ASC


Select SiteName, Time, Type, TimeFin, TypeFin, Details, ResponseTime As EventToSelect, EventToAck, (EventToAck - ResponseTime) AS SelectToAck From
(Select SiteName, Time, Type, Details, ResponseTime
from dbo.Results
Where Type = 'Site Acknowledge') e
RIGHT JOIN #ie ON e.SiteName = #ie.SiteNameFin AND #ie.TimeFin Between e.Time AND DATEADD(second, 5, e.Time)
ORDER BY TimeFin ASC


DROP TABLE #ie


Expected result;



SiteName Time Type TimeFin TypeFin Details EventToSelect EventToAck SelectToAck
Batman 2012-11-14 09:11:41.000 Site Acknowledge 2012-11-14 09:11:41.000 Finished SJH 61695 61700 5
Batman 2012-11-14 09:13:55.000 Site Acknowledge 2012-11-14 09:13:55.000 Finished SJH 11 14 3
NULL NULL NULL 2012-11-14 09:14:18.000 Finished NULL NULL 3 NULL
Batman 2012-11-14 09:16:22.000 Site Acknowledge 2012-11-14 09:16:22.000 Finished SJH 13 16 3
DILBERT 2012-11-14 09:16:29.000 Site Acknowledge 2012-11-14 09:16:29.000 Finished SJH 3 20 17
DILBERT 2012-11-14 09:18:19.000 Site Acknowledge 2012-11-14 09:18:19.000 Finished SJH 4 10 6
DILBERT 2012-11-14 09:20:17.000 Site Acknowledge 2012-11-14 09:20:17.000 Finished SJH 3 8 5
NULL NULL NULL 2012-11-14 09:22:22.000 Finished NULL NULL 86 NULL
DILBERT 2012-11-14 09:22:30.000 Site Acknowledge 2012-11-14 09:22:30.000 Finished SJH 17 21 4
NULL NULL NULL 2012-11-14 09:23:23.000 Finished NULL NULL 48 NULL
Batman 2012-11-14 09:24:15.000 Site Acknowledge 2012-11-14 09:24:16.000 Finished SJH 13 22 9
DILBERT 2012-11-14 09:24:21.000 Site Acknowledge 2012-11-14 09:24:21.000 Finished SJH 8 12 4
Invisible Man 2012-11-14 09:24:45.000 Site Acknowledge 2012-11-14 09:24:45.000 Finished SJH 19 23 4
Batman 2012-11-14 09:25:18.000 Site Acknowledge 2012-11-14 09:25:18.000 Finished SJH 19 28 9
DILBERT 2012-11-14 09:26:17.000 Site Acknowledge 2012-11-14 09:26:17.000 Finished SJH 3 8 5
Invisible Man 2012-11-14 09:26:31.000 Site Acknowledge 2012-11-14 09:26:31.000 Finished SJH 4 9 5
Batman 2012-11-14 09:27:26.000 Site Acknowledge 2012-11-14 09:27:26.000 Finished SJH 3 8 5
Invisible Man 2012-11-14 09:27:33.000 Site Acknowledge 2012-11-14 09:27:38.000 Finished SJH 5 14 9
Invisible Man 2012-11-14 09:27:38.000 Site Acknowledge 2012-11-14 09:27:38.000 Finished SJH 2 14 12
DILBERT 2012-11-14 09:28:17.000 Site Acknowledge 2012-11-14 09:28:17.000 Finished SJH 5 8 3
Invisible Man 2012-11-14 09:36:09.000 Site Acknowledge 2012-11-14 09:36:09.000 Finished SJH 387 391 4
Batman 2012-11-14 09:36:13.000 Site Acknowledge 2012-11-14 09:36:14.000 Finished SJH 304 307 3
DILBERT 2012-11-14 09:36:19.000 Site Acknowledge 2012-11-14 09:36:19.000 Finished SJH 366 370 4
Invisible Man 2012-11-14 09:38:28.000 Site Acknowledge 2012-11-14 09:38:29.000 Finished SJH 45 49 4
Batman 2012-11-14 09:38:32.000 Site Acknowledge 2012-11-14 09:38:33.000 Finished SJH 16 19 3
DILBERT 2012-11-14 09:38:38.000 Site Acknowledge 2012-11-14 09:38:38.000 Finished SJH 24 29 5
Invisible Man 2012-11-14 09:41:22.000 Site Acknowledge 2012-11-14 09:41:22.000 Finished SJH 95 104 9
Batman 2012-11-14 09:41:27.000 Site Acknowledge 2012-11-14 09:41:27.000 Finished SJH 17 21 4
DILBERT 2012-11-14 09:41:32.000 Site Acknowledge 2012-11-14 09:41:32.000 Finished SJH 79 83 4
Invisible Man 2012-11-14 09:41:50.000 Site Acknowledge 2012-11-14 09:41:51.000 Finished SJH 3 12 9
DILBERT 2012-11-14 09:43:13.000 Site Acknowledge 2012-11-14 09:43:14.000 Finished SJH 61 64 3
Batman 2012-11-14 09:58:47.000 Site Acknowledge 2012-11-14 09:58:47.000 Finished SJH 916 920 4
Invisible Man 2012-11-14 09:58:54.000 Site Acknowledge 2012-11-14 09:58:54.000 Finished SJH 795 799 4
DILBERT 2012-11-14 09:59:01.000 Site Acknowledge 2012-11-14 09:59:01.000 Finished SJH 888 892 4
Invisible Man 2012-11-14 10:47:02.000 Site Acknowledge 2012-11-14 10:47:03.000 Finished SJH 2844 2847 3
Batman 2012-11-14 10:47:10.000 Site Acknowledge 2012-11-14 10:47:10.000 Finished SJH 2759 2763 4
DILBERT 2012-11-14 10:47:14.000 Site Acknowledge 2012-11-14 10:47:15.000 Finished SJH 2821 2824 3
Invisible Man 2012-11-14 11:23:53.000 Site Acknowledge 2012-11-14 11:23:53.000 Finished SJH 2170 2178 8
Batman 2012-11-14 11:23:57.000 Site Acknowledge 2012-11-14 11:23:58.000 Finished SJH 1968 1971 3
DILBERT 2012-11-14 11:24:03.000 Site Acknowledge 2012-11-14 11:24:03.000 Finished SJH 2150 2154 4



Here is the result which has the case that is an issue;


Invisible Man 2012-11-14 09:27:33.000 Site Acknowledge 2012-11-14 09:27:38.000 Finished SJH 5 14 9

Invisible Man 2012-11-14 09:27:38.000 Site Acknowledge 2012-11-14 09:27:38.000 Finished SJH 2 14 12


Here is the input which has the case that is an issue;

Time SiteID SiteName SiteTx Type Source Details ResponseTime DurationOnASL
2012-11-14 09:27:33.000 INV_MAN Invisible Man FastTrace-FT-R-FastTx Site Acknowledge User Interface 1 SJH 5 0

Time SiteID SiteName SiteTx Type Source Details ResponseTime DurationOnASL
2012-11-14 09:27:38.000 INV_MAN Invisible Man FastTrace-FT-R-FastTx Site Acknowledge User Interface 1 SJH 2 0

Time SiteID SiteName SiteTx Type Source Details ResponseTime DurationOnASL
2012-11-14 09:27:38.000 INV_MAN Invisible Man FastTrace-FT-R-FastTx Finished SM NULL 0 14

This only works because of this line in the code and the finish is within 5 seconds of both Site Acknowledge events;

AND #ie.TimeFin Between e.Time AND DATEADD(second, 5, e.Time)

What I really need is this to be replaced with something that joins the finish event based on the time for the finish event being the next time along from the site acknowledge event time for the respective sitename.
Any ideas?

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts
Posted - 2012-11-14 : 18:42:56
This code now reveals the following but I am now missing these rows from #ie where there is no site acknowledge row in results. Result below is expected an correct but I am missing the three rows listed at the bottom of this post.

Select SiteName, Time, Type, TimeFin, TypeFin, Details, ResponseTime As EventToSelect, EventToAck, (EventToAck - ResponseTime) AS SelectToAck
from dbo.Results r
OUTER APPLY
(Select Top (1) EventToAck, TimeFin, TypeFin From #ie Where r.SiteName = #ie.SiteNameFin AND #ie.TimeFin >= r.Time Order By Time ASC) s
Where r.Type = 'Site Acknowledge'
ORDER BY r.Time ASC



SiteName Time Type TimeFin TypeFin Details EventToSelect EventToAck SelectToAck
Batman 2012-11-14 09:11:41.000 Site Acknowledge 2012-11-14 09:11:41.000 Finished SJH 61695 61700 5
Batman 2012-11-14 09:13:55.000 Site Acknowledge 2012-11-14 09:13:55.000 Finished SJH 11 14 3
Batman 2012-11-14 09:16:22.000 Site Acknowledge 2012-11-14 09:16:22.000 Finished SJH 13 16 3
DILBERT 2012-11-14 09:16:29.000 Site Acknowledge 2012-11-14 09:16:29.000 Finished SJH 3 20 17
DILBERT 2012-11-14 09:18:19.000 Site Acknowledge 2012-11-14 09:18:19.000 Finished SJH 4 10 6
DILBERT 2012-11-14 09:20:17.000 Site Acknowledge 2012-11-14 09:20:17.000 Finished SJH 3 8 5
DILBERT 2012-11-14 09:22:30.000 Site Acknowledge 2012-11-14 09:22:30.000 Finished SJH 17 21 4
Batman 2012-11-14 09:24:15.000 Site Acknowledge 2012-11-14 09:24:16.000 Finished SJH 13 22 9
DILBERT 2012-11-14 09:24:21.000 Site Acknowledge 2012-11-14 09:24:21.000 Finished SJH 8 12 4
Invisible Man 2012-11-14 09:24:45.000 Site Acknowledge 2012-11-14 09:24:45.000 Finished SJH 19 23 4
Batman 2012-11-14 09:25:18.000 Site Acknowledge 2012-11-14 09:25:18.000 Finished SJH 19 28 9
DILBERT 2012-11-14 09:26:17.000 Site Acknowledge 2012-11-14 09:26:17.000 Finished SJH 3 8 5
Invisible Man 2012-11-14 09:26:31.000 Site Acknowledge 2012-11-14 09:26:31.000 Finished SJH 4 9 5
Batman 2012-11-14 09:27:26.000 Site Acknowledge 2012-11-14 09:27:26.000 Finished SJH 3 8 5
Invisible Man 2012-11-14 09:27:33.000 Site Acknowledge 2012-11-14 09:27:38.000 Finished SJH 5 14 9
Invisible Man 2012-11-14 09:27:38.000 Site Acknowledge 2012-11-14 09:27:38.000 Finished SJH 2 14 12
DILBERT 2012-11-14 09:28:17.000 Site Acknowledge 2012-11-14 09:28:17.000 Finished SJH 5 8 3
Invisible Man 2012-11-14 09:36:09.000 Site Acknowledge 2012-11-14 09:36:09.000 Finished SJH 387 391 4
Batman 2012-11-14 09:36:13.000 Site Acknowledge 2012-11-14 09:36:14.000 Finished SJH 304 307 3
DILBERT 2012-11-14 09:36:19.000 Site Acknowledge 2012-11-14 09:36:19.000 Finished SJH 366 370 4
Invisible Man 2012-11-14 09:38:28.000 Site Acknowledge 2012-11-14 09:38:29.000 Finished SJH 45 49 4
Batman 2012-11-14 09:38:32.000 Site Acknowledge 2012-11-14 09:38:33.000 Finished SJH 16 19 3
DILBERT 2012-11-14 09:38:38.000 Site Acknowledge 2012-11-14 09:38:38.000 Finished SJH 24 29 5
Invisible Man 2012-11-14 09:41:22.000 Site Acknowledge 2012-11-14 09:41:22.000 Finished SJH 95 104 9
Batman 2012-11-14 09:41:27.000 Site Acknowledge 2012-11-14 09:41:27.000 Finished SJH 17 21 4
DILBERT 2012-11-14 09:41:32.000 Site Acknowledge 2012-11-14 09:41:32.000 Finished SJH 79 83 4
Invisible Man 2012-11-14 09:41:50.000 Site Acknowledge 2012-11-14 09:41:51.000 Finished SJH 3 12 9
DILBERT 2012-11-14 09:43:13.000 Site Acknowledge 2012-11-14 09:43:14.000 Finished SJH 61 64 3
Batman 2012-11-14 09:58:47.000 Site Acknowledge 2012-11-14 09:58:47.000 Finished SJH 916 920 4
Invisible Man 2012-11-14 09:58:54.000 Site Acknowledge 2012-11-14 09:58:54.000 Finished SJH 795 799 4
DILBERT 2012-11-14 09:59:01.000 Site Acknowledge 2012-11-14 09:59:01.000 Finished SJH 888 892 4
Invisible Man 2012-11-14 10:47:02.000 Site Acknowledge 2012-11-14 10:47:03.000 Finished SJH 2844 2847 3
Batman 2012-11-14 10:47:10.000 Site Acknowledge 2012-11-14 10:47:10.000 Finished SJH 2759 2763 4
DILBERT 2012-11-14 10:47:14.000 Site Acknowledge 2012-11-14 10:47:15.000 Finished SJH 2821 2824 3
Invisible Man 2012-11-14 11:23:53.000 Site Acknowledge 2012-11-14 11:23:53.000 Finished SJH 2170 2178 8
Batman 2012-11-14 11:23:57.000 Site Acknowledge 2012-11-14 11:23:58.000 Finished SJH 1968 1971 3
DILBERT 2012-11-14 11:24:03.000 Site Acknowledge 2012-11-14 11:24:03.000 Finished SJH 2150 2154 4



So I am now missing these;

NULL NULL NULL 2012-11-14 09:14:18.000 Finished NULL NULL 3 NULL
NULL NULL NULL 2012-11-14 09:22:22.000 Finished NULL NULL 86 NULL
NULL NULL NULL 2012-11-14 09:23:23.000 Finished NULL NULL 48 NULL

Another way of looking at this is that the code at the front on this post joins all the correct site acknowledge events to finished events. But there are three finished events that you can see in my original post that do not have any site acknowledge events. Here is one example;

2012-11-14 09:14:18.000 CHAM Chameleon (PSTN, ISDN, LAN) FastTrace-FT-R-FastTx Finished SM NULL 0 3

From the results table in my original post I am trying to acheive the following three cases.

1. SiteName, Site Acknowledge - joins to the next SiteName, Finished.
2. Site, Site Acknowledge and Site, Site Acknowledge - both join to the next SiteName, Finished.
3. SiteName, Finished - just included.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site