Databases
StackOverflow2013
Postsdbo
POT-SQL join to get both mated and non mated start and stop records
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POT-SQL join to get both mated and non mated start and stop records
    primarykey
    data
    text
    <p>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. </p> <p>There is a <strong><em>single</em></strong> table that surprisingly has a primary key. There is no pivot/intersection table. Structure is </p> <pre><code>ID (int PK) activityType varchar beginEnd varchar ('begin' or 'end') businessKey varchar nullable date DATETIME technician varchar </code></pre> <p>The following columns are in the table as well, but are nullable, and not important to the query.</p> <pre><code>dateSubmitted DATETIME gpsLatitude float gpsLongitude float note varchar odometer int </code></pre> <hr> <p>The query that I have now that still leaves dupes: Showing and sorting ID and EndID are for debugging only</p> <pre><code>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 </code></pre> <hr> <p>DATA:</p> <pre><code>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,barthur@fubar.com 23570,Travel,Begin,00100228002,2010-11-01 07:00:44.000,2010-11-01 08:34:15.370,35.0634,-80.7668,,18706,creneau@fubar.com 23571,Standby,End,,2010-11-01 08:30:08.000,2010-11-01 08:35:20.463,34.0918,-77.9002,#1140,28766,barthur@fubar.com 23572,Travel,Begin,00100226488,2010-11-01 08:30:41.000,2010-11-01 08:36:56.420,34.0918,-77.9002,,28766,barthur@fubar.com 23573,Travel,End,00100226488,2010-11-01 08:45:00.000,2010-11-01 08:44:15.553,34.0918,-77.9002,,28768,barthur@fubar.com 23574,OnSite,Begin,00100226488,2010-11-01 08:45:41.000,2010-11-01 09:24:23.943,34.0918,-77.9002,,0,barthur@fubar.com 23575,OnSite,End,00100226488,2010-11-01 09:30:10.000,2010-11-01 09:33:19.953,34.0918,-77.9002,,28768,barthur@fubar.com 23576,Travel,Begin,00100228137,2010-11-01 09:30:20.000,2010-11-01 09:34:57.330,34.0918,-77.9002,,28768,barthur@fubar.com 23577,Travel,End,00100228137,2010-11-01 09:45:51.000,2010-11-01 09:42:39.230,34.0918,-77.9002,,28771,barthur@fubar.com 23578,Travel,Begin,00100228138,2010-11-01 09:00:23.000,2010-11-01 09:58:22.857,34.9827,-80.5365,,18749,creneau@fubar.com 23579,OnSite,Begin,00100228137,2010-11-01 09:45:47.000,2010-11-01 10:41:10.563,34.139,-77.895,,0,barthur@fubar.com 23580,OnSite,End,00100228137,2010-11-01 10:45:43.000,2010-11-01 11:09:14.393,34.139,-77.895,,28771,barthur@fubar.com 23581,OnSite,Begin,00100228142,2010-11-01 10:45:42.000,2010-11-01 11:29:26.447,34.139,-77.895,#1015,28771,barthur@fubar.com 23582,OnSite,End,00100228142,2010-11-01 11:15:18.000,2010-11-01 11:55:28.603,34.139,-77.895,#1015,28771,barthur@fubar.com 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,barthur@fubar.com 23584,Travel,End,,2010-11-01 12:00:47.000,2010-11-01 12:07:54.867,34.139,-77.895,"#1154, #1142",28774,barthur@fubar.com 23585,Travel,End,,2010-11-01 12:00:47.000,2010-11-01 12:07:55.087,34.139,-77.895,"#1154, #1142",28774,barthur@fubar.com 23586,Break,Begin,,2010-11-01 12:00:26.000,2010-11-01 12:08:06.007,34.139,-77.895,#1153,28774,barthur@fubar.com 23587,Travel,End,,2010-11-01 12:00:47.000,2010-11-01 12:08:06.040,34.139,-77.895,"#1154, #1142",28774,barthur@fubar.com 23588,Break,Begin,,2010-11-01 12:00:26.000,2010-11-01 12:08:06.070,34.139,-77.895,#1153,28774,barthur@fubar.com 23589,Travel,End,,2010-11-01 12:00:47.000,2010-11-01 12:16:02.673,34.139,-77.895,"#1154, #1142",28774,barthur@fubar.com 23590,Travel,End,,2010-11-01 12:00:47.000,2010-11-01 12:16:14.220,34.139,-77.895,"#1154, #1142",28774,barthur@fubar.com 23591,Travel,Begin,00100228000,2010-11-01 11:45:19.000,2010-11-01 12:35:46.363,35.0634,-80.7668,,18760,creneau@fubar.com 23592,Travel,Begin,00100227980,2010-11-01 13:15:14.000,2010-11-01 13:58:51.050,34.0918,-77.9002,,28774,barthur@fubar.com 23593,Travel,Begin,00100227980,2010-11-01 13:15:14.000,2010-11-01 13:59:03.830,34.0918,-77.9002,,28774,barthur@fubar.com 23594,Travel,Begin,00100227980,2010-11-01 13:15:14.000,2010-11-01 13:59:03.893,34.1594,-77.8929,,28774,barthur@fubar.com 23595,Travel,Begin,00100227980,2010-11-01 13:15:14.000,2010-11-01 13:59:03.940,34.1594,-77.8929,,28774,barthur@fubar.com 23596,Travel,Begin,00100227980,2010-11-01 13:15:14.000,2010-11-01 13:59:15.880,34.1594,-77.8929,,28774,barthur@fubar.com 23597,Travel,Begin,00100227980,2010-11-01 13:15:14.000,2010-11-01 13:59:15.927,34.2743,-77.8668,,28774,barthur@fubar.com 23598,Travel,Begin,00100227980,2010-11-01 13:15:14.000,2010-11-01 13:59:15.987,34.2743,-77.8668,,28774,barthur@fubar.com 23599,Travel,Begin,00100228166,2010-11-01 14:00:13.000,2010-11-01 14:29:45.320,35.0634,-80.7668,,18779,creneau@fubar.com 23600,Travel,End,00100227980,2010-11-01 15:15:58.000,2010-11-01 15:15:40.403,35.3414,-78.0325,,28880,barthur@fubar.com 23601,Travel,Begin,00100228205,2010-11-01 15:30:46.000,2010-11-01 15:41:41.810,35.0661,-80.8376,,18781,creneau@fubar.com 23602,OnSite,Begin,00100227980,2010-11-01 15:15:23.000,2010-11-01 15:59:45.203,35.3873,-77.9395,,28880,barthur@fubar.com 23603,OnSite,End,00100227980,2010-11-01 16:15:22.000,2010-11-01 16:06:09.150,35.3873,-77.9395,,28880,barthur@fubar.com 23604,Travel,Begin,00100228007,2010-11-01 16:15:15.000,2010-11-01 16:15:25.253,35.3873,-77.9395,,28880,barthur@fubar.com 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,creneau@fubar.com 23606,Travel,End,00100228007,2010-11-01 16:30:48.000,2010-11-01 16:26:43.360,35.3873,-77.9395,,28884,barthur@fubar.com 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,creneau@fubar.com 23608,OnSite,Begin,00100228007,2010-11-01 16:30:48.000,2010-11-01 18:38:32.700,35.3941,-77.994,,28880,barthur@fubar.com 23609,Travel,Begin,00100228209,2010-11-01 17:45:16.000,2010-11-01 18:39:05.683,35.3941,-77.994,,28884,barthur@fubar.com 23610,OnSite,End,00100228007,2010-11-01 17:45:52.000,2010-11-01 18:41:36.980,35.3941,-77.994,,28884,barthur@fubar.com 23611,OnSite,Begin,00100228209,2010-11-01 18:00:38.000,2010-11-01 18:42:12.763,35.3941,-77.994,,28888,barthur@fubar.com 23612,OnSite,End,00100228209,2010-11-01 18:30:44.000,2010-11-01 18:43:29.123,35.3941,-77.994,,28888,barthur@fubar.com 23613,Standby,Begin,,2010-11-01 18:30:58.000,2010-11-01 18:45:28.857,35.3941,-77.994,#1157 ergo,28888,barthur@fubar.com 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,barthur@fubar.com 23615,Travel,Begin,,2010-11-01 18:45:24.000,2010-11-01 18:47:37.803,35.3941,-77.994,RTN,28888,barthur@fubar.com 23616,Travel,End,,2010-11-01 20:45:05.000,2010-11-01 20:34:39.433,34.139,-77.895,#1142 Fueled,28990,barthur@fubar.com </code></pre> <p>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.<br> <img src="https://i.stack.imgur.com/wizhs.png" alt="enter image description here"></p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USJoe Johnston
    1. USJoe Johnston
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POT-SQL join to get both mated and non mated start and stop records
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POT-SQL join to get both mated and non mated start and stop records
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POT-SQL join to get both mated and non mated start and stop records
      1. USJoe Johnston
      1. VTBountyStart
    1. CO_The problem is that I have duplicate entries were the entry should be marked as having no mate._ Are these exact duplicates? If not, what defines a duplicate? How do we know which begin and end records to match, and which to mark as mateless?
      singulars
      1. POT-SQL join to get both mated and non mated start and stop records
      1. USShannon Severance
    2. COA duplicate is anytime a begin or end record is reported more than once. in the image 9-13 are dupes as 23583 has already been reported. So 9-13 should be ends without a matching begin. Like 14 and 15 are begins without ends.
      singulars
      1. POT-SQL join to get both mated and non mated start and stop records
      1. USJoe Johnston
    3. CO@JoeJohnston, could you possibly update the "DATA" section to include id and correct dates (all dates look like times w/o hours)? Records corresponding to your output image would be most helpful.
      singulars
      1. POT-SQL join to get both mated and non mated start and stop records
      1. USTim Lehner
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload