Weird Duplicate Result from Query

Page 1 of 1

2 Replies - 812 Views - Last Post: 06 November 2015 - 09:34 AM Rate Topic: -----

#1 ybadragon   User is offline

  • Home Owner
  • member icon

Reputation: 571
  • View blog
  • Posts: 2,647
  • Joined: 11-May 12

Weird Duplicate Result from Query

Posted 06 November 2015 - 09:00 AM

So we have inspectors who are in charge of doing things to vehicles. These things are called interactions. What they consider an inspection is every interaction done to a vehicle up to one that has an event of 'Complete'. Anything after that is a new inspection. The issue is what they send us is an 'Assignment' which holds multiple inspections that aren't separated by an id, and instead are only separated by an assignment id and interaction ids. So in order to split their data for them I have to create a table called inspections and write the logic that splits those as they come in based on the complete interactions.

I have all of that done except for 1 issue that is confusing the hell out of me and it is a duplicate entry. It's a lot of data so I'll try to break it apart using images with the results of each step and explanations above them. I have limited my results to 1 Assignment that will have 3 inspections in the end and consists of 4 total interactions on the assignment. If you have a better way of doing something let me know, because this query takes a while to run when going over 33000 interactions based on a 2 day range.

Step 1: Get a list of Filtered Interactions
QUERY:
DECLARE @StartTime datetime = CONVERT(DATETIME, '2015-07-20')
DECLARE @EndTime datetime = CONVERT(DATETIME, '2015-07-24')

CREATE TABLE #FilteredInteractions
(
	InteractionID BIGINT NULL,
	LocationID INT NULL,
	AssignmentID BIGINT NULL,
	InteractionEvent NVARCHAR(30) NULL,
	StartTime DATETIME NULL,
	EndTime DATETIME NULL
);
INSERT INTO #FilteredInteractions
SELECT ii.InteractionId, ia.LocationId, ia.AssignmentId, ii.InteractionEvent, ii.StartTime, ii.EndTime 
FROM InspInteractions ii
LEFT OUTER JOIN InspAssignments ia
ON ii.AssignmentId = ia.AssignmentId
WHERE (ii.StartTime >= @StartTime OR @StartTime IS NULL)
AND (ii.EndTime < @EndTime OR @EndTime IS NULL)

SELECT * FROM #FilteredInteractions
WHERE AssignmentID = 3133200;


RESULT
Posted Image

STEP 2: Get a list of all the Interactions marked 'Complete' and assign them an InspectionID that I generate on the fly.
CREATE TABLE #InspectionsByCompleteDate
(
	AssignmentID BIGINT NULL,
	InspectionID UNIQUEIDENTIFIER NOT NULL,
	LastInteractionID BIGINT NULL,
	CompleteDateStart DATETIME NULL,
	CompleteDateEnd DATETIME NULL,
	CONSTRAINT [PK_Inspection_InspectionID] PRIMARY KEY CLUSTERED ([InspectionID]),
);
INSERT INTO #InspectionsByCompleteDate
SELECT AssignmentId, NEWID(), InteractionId LastInteractionID, StartTime CompleteDateStart, EndTime CompleteDateEnd
FROM #FilteredInteractions
WHERE InteractionEvent = 'Complete'
AND AssignmentID = 3133200

SELECT * FROM #InspectionsByCompleteDate


RESULT:
Posted Image

STEP 3: Create a temp table for holding the inspections (this will be a real table after I get this issue worked out). Also declare some variables for comparison in the upcoming steps
CREATE TABLE #Inspections
(
	InspectionID NVARCHAR(128) NULL,
	AssignmentID BIGINT NULL,
	InteractionID BIGINT NULL,
);

DECLARE @CurInspectionID NVARCHAR(128)
DECLARE @CurAssignmentID BIGINT
DECLARE @CurInteractionID BIGINT
DECLARE @PrevInspectionID NVARCHAR(128)
DECLARE @PrevAssignmentID BIGINT
DECLARE @PrevInteractionID BIGINT


STEP 4: Loop through the InspectionsByCompleteDate table and get the current values and the previous values for an inspection based on the assignmentID
WHILE EXISTS(SELECT 1 FROM #InspectionsByCompleteDate)
BEGIN
	
	-- GET CURRENT VALUES
	SELECT TOP (1) @CurAssignmentID = ibcd.AssignmentID, @CurInspectionID = ibcd.InspectionID, @CurInteractionID = ibcd.LastInteractionID FROM #InspectionsByCompleteDate ibcd
	ORDER BY ibcd.CompleteDateStart DESC;

	--GET Previous Values
	SELECT TOP (1) @PrevAssignmentID = ibcd.AssignmentID, @PrevInspectionID = ibcd.InspectionID, @PrevInteractionID = ibcd.LastInteractionID FROM #InspectionsByCompleteDate ibcd
	WHERE ibcd.LastInteractionID < @CurInteractionID AND ibcd.AssignmentID = @CurAssignmentID ORDER BY ibcd.CompleteDateStart DESC;

	SELECT @CurAssignmentID CurAssignment, @CurInspectionID CurInspection, @CurInteractionID CurInteraction, 
		   @PrevAssignmentID PrevAssignment, @PrevInspectionID PrevInspection, @PrevInteractionID PrevInteraction;


RESULTS: The results vary based on how many complete interactions there were on an assignment
Posted Image

STEP 5: Insert all interactions from #FilteredInteractions into the #Inspections table where the interactionID > the previous interaction id and greater than or equal to the current interaction id. Then if the previous interaction id is null insert everything for that assignment with an interactionID greater than the currentinteractionid but with a new inspection id because everything after the last complete interaction is a new inspection. Then set all variables to null for the next iteraction through the loop.

DELETE FROM #InspectionsByCompleteDate
	WHERE LastInteractionID = @CurInteractionID;

	INSERT INTO #Inspections
	SELECT @CurInspectionID InspectionID, ii.AssignmentId, ii.InteractionID 
	FROM #FilteredInteractions ii
	WHERE ii.AssignmentId = @CurAssignmentID AND (ii.InteractionId > @PrevInteractionID Or @PrevInteractionID IS NULL) AND ii.InteractionId <= @CurInteractionID
	
	IF @PrevInteractionID IS NULL
	BEGIN
		DECLARE @TempInspectionID UNIQUEIDENTIFIER = NEWID()
		INSERT INTO #Inspections
		SELECT @TempInspectionID, ii.AssignmentId, ii.InteractionID
		FROM #FilteredInteractions ii
		WHERE ii.AssignmentId = @CurAssignmentID AND ii.InteractionID > @CurInteractionID
	END

	SELECT @CurAssignmentID = NULL, @CurInspectionID= NULL, @CurInteractionID = NULL, @PrevAssignmentID = NULL, @PrevInspectionID = NULL, @PrevInteractionID = NULL
END

SELECT * FROM #Inspections
ORDER BY InteractionID


RESULTS: As you can see the InteractionDI 11642262 is duplicated which from what I see shouldn't be possible and is the issue I'm having.
Posted Image


BELOW IS THE ENTIRE QUERY PUT TOGETHER
Spoiler


Is This A Good Question/Topic? 0
  • +

Replies To: Weird Duplicate Result from Query

#2 ybadragon   User is offline

  • Home Owner
  • member icon

Reputation: 571
  • View blog
  • Posts: 2,647
  • Joined: 11-May 12

Re: Weird Duplicate Result from Query

Posted 06 November 2015 - 09:29 AM

I figured it out, I needed to delete from #FilteredInspections WHERE in interactionid was in the inspections table. I limited it to 3 assignments instead of 1 for more results.

DECLARE @StartTime datetime = CONVERT(DATETIME, '2015-07-20')
DECLARE @EndTime datetime = CONVERT(DATETIME, '2015-07-24')

CREATE TABLE #FilteredInteractions
(
	InteractionID BIGINT NULL,
	LocationID INT NULL,
	AssignmentID BIGINT NULL,
	InteractionEvent NVARCHAR(30) NULL,
	StartTime DATETIME NULL,
	EndTime DATETIME NULL
);
INSERT INTO #FilteredInteractions
SELECT ii.InteractionId, ia.LocationId, ia.AssignmentId, ii.InteractionEvent, ii.StartTime, ii.EndTime 
FROM InspInteractions ii
LEFT OUTER JOIN InspAssignments ia
ON ii.AssignmentId = ia.AssignmentId
WHERE --(ia.LocationID = @LocationID OR @LocationID IS NULL OR @LocationID = -1 OR @LocationID = '')
(ii.StartTime >= @StartTime OR @StartTime IS NULL)
AND (ii.EndTime < @EndTime OR @EndTime IS NULL)

SELECT * FROM #FilteredInteractions

CREATE TABLE #InspectionsByCompleteDate
(
	AssignmentID BIGINT NULL,
	InspectionID UNIQUEIDENTIFIER NOT NULL,
	LastInteractionID BIGINT NULL,
	CompleteDateStart DATETIME NULL,
	CompleteDateEnd DATETIME NULL,
	CONSTRAINT [PK_Inspection_InspectionID] PRIMARY KEY CLUSTERED ([InspectionID]),
);
INSERT INTO #InspectionsByCompleteDate
SELECT AssignmentId, NEWID(), InteractionId LastInteractionID, StartTime CompleteDateStart, EndTime CompleteDateEnd  --NULL PreviousInteractionID 
FROM #FilteredInteractions
WHERE InteractionEvent = 'Complete'
AND AssignmentId IN (3136477, 3095715, 3126240);

SELECT * FROM #InspectionsByCompleteDate

CREATE TABLE #Inspections
(
	InspectionID NVARCHAR(128) NULL,
	AssignmentID BIGINT NULL,
	InteractionID BIGINT NULL,
);

DECLARE @CurInspectionID NVARCHAR(128)
DECLARE @CurAssignmentID BIGINT
DECLARE @CurInteractionID BIGINT
DECLARE @PrevInspectionID NVARCHAR(128)
DECLARE @PrevAssignmentID BIGINT
DECLARE @PrevInteractionID BIGINT

WHILE EXISTS(SELECT 1 FROM #InspectionsByCompleteDate)
BEGIN
	
	-- GET CURRENT VALUES
	SELECT TOP (1) @CurAssignmentID = ibcd.AssignmentID, @CurInspectionID = ibcd.InspectionID, @CurInteractionID = ibcd.LastInteractionID FROM #InspectionsByCompleteDate ibcd
	ORDER BY ibcd.CompleteDateStart DESC;

	--GET Previous Values
	SELECT TOP (1) @PrevAssignmentID = ibcd.AssignmentID, @PrevInspectionID = ibcd.InspectionID, @PrevInteractionID = ibcd.LastInteractionID FROM #InspectionsByCompleteDate ibcd
	WHERE ibcd.LastInteractionID < @CurInteractionID AND ibcd.AssignmentID = @CurAssignmentID ORDER BY ibcd.CompleteDateStart DESC;

	SELECT @CurAssignmentID CurAssignment, @CurInspectionID CurInspection, @CurInteractionID CurInteraction, 
		   @PrevAssignmentID PrevAssignment, @PrevInspectionID PrevInspection, @PrevInteractionID PrevInteraction;

	DELETE FROM #InspectionsByCompleteDate
	WHERE LastInteractionID = @CurInteractionID;

	INSERT INTO #Inspections
	SELECT @CurInspectionID InspectionID, ii.AssignmentId, ii.InteractionID 
	FROM #FilteredInteractions ii
	WHERE ii.AssignmentId = @CurAssignmentID AND (ii.InteractionId > @PrevInteractionID Or @PrevInteractionID IS NULL) AND ii.InteractionId <= @CurInteractionID

	DELETE FROM #FilteredInteractions WHERE InteractionID IN (SELECT InteractionID FROM #Inspections)
	
	IF @PrevInteractionID IS NULL
	BEGIN
		DECLARE @TempInspectionID UNIQUEIDENTIFIER = NEWID()
		INSERT INTO #Inspections
		SELECT @TempInspectionID, ii.AssignmentId, ii.InteractionID
		FROM #FilteredInteractions ii
		WHERE ii.AssignmentId = @CurAssignmentID AND ii.InteractionID > @CurInteractionID
	END
	
	DELETE FROM #FilteredInteractions WHERE InteractionID IN (SELECT InteractionID FROM #Inspections)

	SELECT @CurAssignmentID = NULL, @CurInspectionID= NULL, @CurInteractionID = NULL, @PrevAssignmentID = NULL, @PrevInspectionID = NULL, @PrevInteractionID = NULL
END

SELECT * FROM #Inspections
ORDER BY AssignmentID, InspectionID, InteractionID

DROP TABLE #Inspections
DROP TABLE #InspectionsByCompleteDate
DROP TABLE #FilteredInteractions


RESULT
Posted Image

This post has been edited by ybadragon: 06 November 2015 - 09:33 AM

Was This Post Helpful? 0
  • +
  • -

#3 ybadragon   User is offline

  • Home Owner
  • member icon

Reputation: 571
  • View blog
  • Posts: 2,647
  • Joined: 11-May 12

Re: Weird Duplicate Result from Query

Posted 06 November 2015 - 09:34 AM

sorry, I keep having weird posting issues
Was This Post Helpful? 0
  • +
  • -
Page 1 of 1