T-SQL join to get both mated and non mated start and stop records

Question

I have a poorly designed table that I did not design and cannot fix/change because a 3rd party blackberry app that writes to it. The meat is that there is a start record and a stop record for tracking events with NO connection or validation that there is a match. The blackberry app does nothing to tie these records together. I have tried to create a join on its self and create temp tables with the begin and one with the end to full outer join them. The problem is that I have duplicate entries were the entry should be marked as having no mate. Existing data has rows with no mate on both the start and end records. I have searched SO for answers and I found some close answers that have led me this far. I know its a long post, sorry for that.

There is a single table that surprisingly has a primary key. There is no pivot/intersection table. Structure is

ID (int PK)
activityType varchar
beginEnd varchar ('begin' or 'end')
businessKey varchar nullable
date DATETIME
technician varchar

The following columns are in the table as well, but are nullable, and not important to the query.

dateSubmitted DATETIME
gpsLatitude float
gpsLongitude float
note varchar
odometer int

---

The query that I have now that still leaves dupes: Showing and sorting ID and EndID are for debugging only

DECLARE @DateFrom DATETIME
DECLARE @DateTo DATETIME
SET @DateFrom='20101101'
SET @DateTo='20101102'
DECLARE @Incomplete VARCHAR(15)
SET @Incomplete = 'Incomplete'

DECLARE @StartEvents TABLE
(
[id] [numeric](19, 0) NOT NULL,
[activityType] [varchar](255) NOT NULL,
[beginEnd] [varchar](255) NULL,
[businessKey] [varchar](255) NULL,
[date] [datetime] NOT NULL,
[dateSubmitted] [datetime] NULL,
[gpsLatitude] [float] NULL,
[gpsLongitude] [float] NULL,
[note] [varchar](255) NULL,
[odometer] [int] NULL,
[technician] [varchar](255) NOT NULL
)

INSERT     @StartEvents
       ([ID],[activityType],[beginEnd],[businessKey],[date],[dateSubmitted],[gpsLatitude]
            ,[gpsLongitude],[note],[odometer],[technician])
SELECT      *
FROM        dbo.TimeEntry
WHERE 
[date] between @DateFrom AND @DateTo
AND beginEnd = 'Begin'
--AND [technician] = 'FRED'
ORDER by technician
------------------------------------------------------------
DECLARE @EndEvents TABLE
(
[id] [numeric](19, 0) NOT NULL,
[activityType] [varchar](255) NOT NULL,
[beginEnd] [varchar](255) NULL,
[businessKey] [varchar](255) NULL,
[date] [datetime] NOT NULL,
[dateSubmitted] [datetime] NULL,
[gpsLatitude] [float] NULL,
[gpsLongitude] [float] NULL,
[note] [varchar](255) NULL,
[odometer] [int] NULL,
[technician] [varchar](255) NOT NULL
)

INSERT     @EndEvents
       ([ID],[activityType],[beginEnd],[businessKey],[date],[dateSubmitted],[gpsLatitude]
            ,[gpsLongitude],[note],[odometer],[technician])
SELECT      *
FROM        dbo.TimeEntry
WHERE 
[date] between @DateFrom AND @DateTo AND
beginEnd = 'End'
--AND [technician] = 'FRED'
ORDER by technician

-- And then a conventional SELECT
SELECT     
StartEvents.id 
,EndEvents.id AS EndID
,COALESCE(
    StartEvents.activityType ,EndEvents.activityType ,'Not Available'
    ) AS ActivityType
--,StartEvents.beginEnd as [Begin] 
--,EndEvents.beginEnd AS [End]
,COALESCE (
    convert(VARCHAR(12), StartEvents.[date], 103), 
    convert(VARCHAR(12), EndEvents.[date], 103), @Incomplete
    ) as [Event Date] 
,COALESCE (
    convert(VARCHAR(12), EndEvents.[date], 103), @Incomplete 
    ) as [End Date] 
,COALESCE(
    CONVERT(VARCHAR(5) , StartEvents.dateSubmitted , 108) , @Incomplete
        ) AS StartTime
,COALESCE(
    CONVERT(VARCHAR(5) , EndEvents.dateSubmitted , 108) , @Incomplete
        ) AS EndTime
,COALESCE(
    StartEvents.note, EndEvents.note, ''
    ) as [Note]
,COALESCE(
    StartEvents.technician,EndEvents.technician,'Not Available'
    ) AS Technician 

FROM         
@StartEvents As StartEvents 

FULL OUTER JOIN

@EndEvents AS EndEvents ON 
StartEvents.technician = EndEvents.technician AND 
StartEvents.businessKey = EndEvents.businessKey AND 
StartEvents.activityType = EndEvents.activityType 
AND convert(VARCHAR(12), StartEvents.[date], 103) = convert(VARCHAR(12), EndEvents.[date], 103) 

-- WHERE 
    --StartEvents.[date] between @DateFrom AND @DateTo OR 
    --StartEvents.[dateSubmitted] between @DateFrom AND @DateTo 
ORDER BY 
    StartEvents.Technician,
    ID,ENDID

---

DATA:

id,activityType,beginEnd,businessKey,date,dateSubmitted,gpsLatitude,gpsLongitude,note,odometer,technician
23569,Standby,Begin,,2010-11-01 08:00:13.000,2010-11-01 08:26:45.533,34.139,-77.895,#1140,28766,[email protected]
23570,Travel,Begin,00100228002,2010-11-01 07:00:44.000,2010-11-01 08:34:15.370,35.0634,-80.7668,,18706,[email protected]
23571,Standby,End,,2010-11-01 08:30:08.000,2010-11-01 08:35:20.463,34.0918,-77.9002,#1140,28766,[email protected]
23572,Travel,Begin,00100226488,2010-11-01 08:30:41.000,2010-11-01 08:36:56.420,34.0918,-77.9002,,28766,[email protected]
23573,Travel,End,00100226488,2010-11-01 08:45:00.000,2010-11-01 08:44:15.553,34.0918,-77.9002,,28768,[email protected]
23574,OnSite,Begin,00100226488,2010-11-01 08:45:41.000,2010-11-01 09:24:23.943,34.0918,-77.9002,,0,[email protected]
23575,OnSite,End,00100226488,2010-11-01 09:30:10.000,2010-11-01 09:33:19.953,34.0918,-77.9002,,28768,[email protected]
23576,Travel,Begin,00100228137,2010-11-01 09:30:20.000,2010-11-01 09:34:57.330,34.0918,-77.9002,,28768,[email protected]
23577,Travel,End,00100228137,2010-11-01 09:45:51.000,2010-11-01 09:42:39.230,34.0918,-77.9002,,28771,[email protected]
23578,Travel,Begin,00100228138,2010-11-01 09:00:23.000,2010-11-01 09:58:22.857,34.9827,-80.5365,,18749,[email protected]
23579,OnSite,Begin,00100228137,2010-11-01 09:45:47.000,2010-11-01 10:41:10.563,34.139,-77.895,,0,[email protected]
23580,OnSite,End,00100228137,2010-11-01 10:45:43.000,2010-11-01 11:09:14.393,34.139,-77.895,,28771,[email protected]
23581,OnSite,Begin,00100228142,2010-11-01 10:45:42.000,2010-11-01 11:29:26.447,34.139,-77.895,#1015,28771,[email protected]
23582,OnSite,End,00100228142,2010-11-01 11:15:18.000,2010-11-01 11:55:28.603,34.139,-77.895,#1015,28771,[email protected]
23583,Travel,Begin,,2010-11-01 11:15:06.000,2010-11-01 11:56:01.633,34.139,-77.895,"#1142 Fuel, #1154 Tickets",28771,[email protected]
23584,Travel,End,,2010-11-01 12:00:47.000,2010-11-01 12:07:54.867,34.139,-77.895,"#1154, #1142",28774,[email protected]
23585,Travel,End,,2010-11-01 12:00:47.000,2010-11-01 12:07:55.087,34.139,-77.895,"#1154, #1142",28774,[email protected]
23586,Break,Begin,,2010-11-01 12:00:26.000,2010-11-01 12:08:06.007,34.139,-77.895,#1153,28774,[email protected]
23587,Travel,End,,2010-11-01 12:00:47.000,2010-11-01 12:08:06.040,34.139,-77.895,"#1154, #1142",28774,[email protected]
23588,Break,Begin,,2010-11-01 12:00:26.000,2010-11-01 12:08:06.070,34.139,-77.895,#1153,28774,[email protected]
23589,Travel,End,,2010-11-01 12:00:47.000,2010-11-01 12:16:02.673,34.139,-77.895,"#1154, #1142",28774,[email protected]
23590,Travel,End,,2010-11-01 12:00:47.000,2010-11-01 12:16:14.220,34.139,-77.895,"#1154, #1142",28774,[email protected]
23591,Travel,Begin,00100228000,2010-11-01 11:45:19.000,2010-11-01 12:35:46.363,35.0634,-80.7668,,18760,[email protected]
23592,Travel,Begin,00100227980,2010-11-01 13:15:14.000,2010-11-01 13:58:51.050,34.0918,-77.9002,,28774,[email protected]
23593,Travel,Begin,00100227980,2010-11-01 13:15:14.000,2010-11-01 13:59:03.830,34.0918,-77.9002,,28774,[email protected]
23594,Travel,Begin,00100227980,2010-11-01 13:15:14.000,2010-11-01 13:59:03.893,34.1594,-77.8929,,28774,[email protected]
23595,Travel,Begin,00100227980,2010-11-01 13:15:14.000,2010-11-01 13:59:03.940,34.1594,-77.8929,,28774,[email protected]
23596,Travel,Begin,00100227980,2010-11-01 13:15:14.000,2010-11-01 13:59:15.880,34.1594,-77.8929,,28774,[email protected]
23597,Travel,Begin,00100227980,2010-11-01 13:15:14.000,2010-11-01 13:59:15.927,34.2743,-77.8668,,28774,[email protected]
23598,Travel,Begin,00100227980,2010-11-01 13:15:14.000,2010-11-01 13:59:15.987,34.2743,-77.8668,,28774,[email protected]
23599,Travel,Begin,00100228166,2010-11-01 14:00:13.000,2010-11-01 14:29:45.320,35.0634,-80.7668,,18779,[email protected]
23600,Travel,End,00100227980,2010-11-01 15:15:58.000,2010-11-01 15:15:40.403,35.3414,-78.0325,,28880,[email protected]
23601,Travel,Begin,00100228205,2010-11-01 15:30:46.000,2010-11-01 15:41:41.810,35.0661,-80.8376,,18781,[email protected]
23602,OnSite,Begin,00100227980,2010-11-01 15:15:23.000,2010-11-01 15:59:45.203,35.3873,-77.9395,,28880,[email protected]
23603,OnSite,End,00100227980,2010-11-01 16:15:22.000,2010-11-01 16:06:09.150,35.3873,-77.9395,,28880,[email protected]
23604,Travel,Begin,00100228007,2010-11-01 16:15:15.000,2010-11-01 16:15:25.253,35.3873,-77.9395,,28880,[email protected]
23605,Travel,Begin,,2010-11-01 16:15:12.000,2010-11-01 16:20:49.933,35.0445,-80.8227,Return trip home,18785,[email protected]
23606,Travel,End,00100228007,2010-11-01 16:30:48.000,2010-11-01 16:26:43.360,35.3873,-77.9395,,28884,[email protected]
23607,Travel,End,,2010-11-01 17:30:14.000,2010-11-01 17:23:57.897,35.2724,-81.1577,Return trip home,18822,[email protected]
23608,OnSite,Begin,00100228007,2010-11-01 16:30:48.000,2010-11-01 18:38:32.700,35.3941,-77.994,,28880,[email protected]
23609,Travel,Begin,00100228209,2010-11-01 17:45:16.000,2010-11-01 18:39:05.683,35.3941,-77.994,,28884,[email protected]
23610,OnSite,End,00100228007,2010-11-01 17:45:52.000,2010-11-01 18:41:36.980,35.3941,-77.994,,28884,[email protected]
23611,OnSite,Begin,00100228209,2010-11-01 18:00:38.000,2010-11-01 18:42:12.763,35.3941,-77.994,,28888,[email protected]
23612,OnSite,End,00100228209,2010-11-01 18:30:44.000,2010-11-01 18:43:29.123,35.3941,-77.994,,28888,[email protected]
23613,Standby,Begin,,2010-11-01 18:30:58.000,2010-11-01 18:45:28.857,35.3941,-77.994,#1157 ergo,28888,[email protected]
23614,Standby,End,,2010-11-01 18:45:26.000,2010-11-01 18:46:01.167,35.3941,-77.994,#1157 ergo redo,28888,[email protected]
23615,Travel,Begin,,2010-11-01 18:45:24.000,2010-11-01 18:47:37.803,35.3941,-77.994,RTN,28888,[email protected]
23616,Travel,End,,2010-11-01 20:45:05.000,2010-11-01 20:34:39.433,34.139,-77.895,#1142 Fueled,28990,[email protected]

In this image you see that the highlighted rows are showing 6 end times with the same begin times. and records 14 and 15 show 2 begins and no ends.

Answer 1

Here is a method which abuses the row_number function. Please check the comments for some explanation.

;with Seq as (
    -- Create a master sequence of events
    -- Trust the date column to be accurate (don't match a Begin to an earlier End)
    select id, activitytype, beginend
        , coalesce(businesskey, '') as businesskey -- Needed to match nulls as equal
        , [date], technician, note
        , row_number() over (partition by technician, businesskey, activitytype order by [date], beginend, id) as rownumber
    from TimeEntry
)
select b.id as BeginID
    , e.id as EndID
    , coalesce(b.technician, e.technician) as Technician
    , coalesce(b.businesskey, e.businesskey) as BusinessKey
    , coalesce(b.activitytype, e.activitytype) as ActivityType
    , coalesce(convert(char(10), b.[date], 103), 'Incomplete') as BeginDate
    , coalesce(convert(char(10), e.[date], 103), 'Incomplete') as EndDate
    , coalesce(convert(char(5), b.[date], 108), 'Incomplete') as BeginTime
    , coalesce(convert(char(5), e.[date], 108), 'Incomplete') as EndTime
    , b.note as BeginNote
    , e.note as EndNote
from (select * from Seq where beginend = 'Begin') b -- Get all Begins
    full outer join (select * from Seq where beginend = 'End') e -- Get all Ends
        on b.technician = e.technician
            and b.businesskey = e.businesskey
            and b.activitytype = e.activitytype
            and b.rownumber = e.rownumber - 1 -- Match a Begin with only the very next End of that type
order by coalesce(b.[date], e.[date])
    , coalesce(b.id, e.id)
    , coalesce(b.technician, e.technician)
    , coalesce(b.businesskey, e.businesskey)
    , coalesce(b.activitytype, e.activitytype)

And the SQL Fiddle should anyone want the DDL or demo.