T-SQL Inspects at a Locaton

I am going to try this fiddle thing. I did not do very much as far as inserting sample data because it gave me errors and erased my work. So Widget Fiddle

These two tables are barely related. One tracks inspections done on a widget and the other tracks where the widget is in the factory as of a specific date and timeamong other things.

What I need to know is where the widget was at the time a specific inspection occurred. A widget event could be anything but when it moves to a different part of the factory the locationID is not null. Otherwise it is null.

Inspections can occur at anytime in any location. So they are really only related by the fact that the widgetIDs match.

What I am looking for is something like. For all inspections show me the location the widget was in when it was performed.

I have seen something like select widgetID, InspectionDatTime, InspectionResult, LocationID
WHERE InspectionDatTime > EventDateTime

And something to do with ROW_NUMBER() = 1

I do not believe the results because for each widget there are multiple locations yet when I use this logic in relation to the inspections I get the same location every time. This is possible, but not probable as each partly built widget usually gets an inspection when they arrive and another at least one more some time later in the process. Sometimes it could be just the one though.

As it relates to the query I also have a problem with duplicate inspections occurring at the same exact date and time probably an anomaly I have to check into but I there is a prebuilt function that could presumably give me the information I wanted but again I do not believe it. It takes a TVP as the argument and inserting into the TVP variable forbids duplicate WidgetIDs and DateTimes. It is generic function and could be used to return a location for anything that has a widgetID and a datetime.

Hope this is explanatory enough.

Thanks
phil916Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
So, for now you have something like this:
SELECT I.WidgetID, I.InspectionPerformedDateTime, I.InspectionResult, E.LocationID
FROM WidgetInspections I
  INNER JOIN  WidgetEvents E
  ON E.WidgetID=I.WidgetID
WHERE I.InspectionPerformedDateTime > E.EventDateTime

Open in new window

What this returns and what are the data that you are expecting to be returned?
0
phil916Author Commented:
OK but what happens if there are non location events, or more than one location events in the sane day?
0
John_VidmarCommented:
Test data:
CREATE TABLE WidgetInspections
(	WidgetID			VARCHAR(30)
,	InspectionResult		VARCHAR(30)
,	InspectionPerformedDateTime	DATETIME
);
  
CREATE TABLE WidgetEvents
(	WidgetID	VARCHAR(30)
,	CodeID		VARCHAR(30)
,	LocationID	VARCHAR(30)
,	EventDateTime	DATETIME
);

insert WidgetEvents select '1','11','loc1','01/01/2011'
insert WidgetEvents select '1','12','loc2','02/02/2011'
insert WidgetEvents select '1','13','loc3','03/03/2011'
insert WidgetEvents select '1','14','loc4','04/04/2011'
insert WidgetEvents select '1','15','loc5','05/05/2011'
insert WidgetEvents select '1','16','loc6','06/06/2011'

insert WidgetInspections select '1','good','02/12/2011'
insert WidgetInspections select '1','good','04/14/2011'
insert WidgetInspections select '1','good','06/16/2011'

Open in new window

Solution:
WITH	CTE
AS
(	SELECT	*
	,	rn = ROW_NUMBER() OVER	(	PARTITION BY	WidgetID
						ORDER BY	EventDateTime
					)
	FROM	WidgetEvents
)
SELECT	a.*
,	b.LocationID
FROM	WidgetInspections	a
JOIN	CTE			b	ON	a.WidgetID = b.WidgetID
LEFT
JOIN	CTE			c	ON	b.WidgetID = c.WidgetID
					AND	b.rn + 1 = c.rn
WHERE	a.InspectionPerformedDateTime
	BETWEEN	b.EventDateTime
	AND	ISNULL(c.EventDateTime,current_timestamp)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

phil916Author Commented:
John_Vidmar,

Without more explanation the code you provided does not scale. It performs poorly (at all? did not wait long enough).

I am not sure what part is run first with a CTE so I am not sure I am providing the correct predicates to get it to work.
0
phil916Author Commented:
OK so a better explanation of the what is needed is in order. I understand now that a CTE is basically a derived table but can then be used in more flexible ways than a sub query type derived table, to be brief. To me that means that the CTE expression is fully evaluated and a result set is returned (maybe not physically but logically?) and the main query is then able to use the CTE in joins etc. as noted in the example. Problem the CTE in this case is way to big. There are thousands of widgets with dozens of events each, without a filter the CTE would be too big?

Schema extension:

CREATE TABLE WidgetInspections
(	WidgetID			VARCHAR(30)
,	InspectionResult		VARCHAR(30)
,       InspectionComponent VARCHAR(30)
,	InspectionPerformedDateTime	DATETIME
);
  
CREATE TABLE WidgetEvents
(	WidgetID	VARCHAR(30)
,	CodeID		VARCHAR(30)
,	LocationID	VARCHAR(30)
,	EventDateTime	DATETIME
);

insert WidgetEvents select '1','11','loc1','01/01/2011'
insert WidgetEvents select '1','12','loc2','02/02/2011'
insert WidgetEvents select '1','13','loc3','03/03/2011'
insert WidgetEvents select '1','14','loc4','04/04/2011'
insert WidgetEvents select '1','15','loc5','05/05/2011'
insert WidgetEvents select '1','16','loc6','06/06/2011'

insert WidgetInspections select '1','good','CIRC','02/12/2011'
insert WidgetInspections select '1','good','CHASSIS','04/14/2011'
insert WidgetInspections select '1','good','MOUNT','06/16/2011'
insert WidgetInspections select '2','good','CIRC','02/12/2011'
insert WidgetInspections select '2','good','CHASSIS','04/14/2011'
insert WidgetInspections select '2','good','MOUNT','06/16/2011'                                          

Open in new window


The more important aspect is that I need to know when a particular component inspection was performed in a given date range and also keep this loose connecton with the location so I know where it was performed.

Hope this is clearer.
0
John_VidmarCommented:
I used the Common Table Expression (CTE) to order the data, assuming that there are many Events, but fewer Inspections.  The data order is important because I also assumed that the Events determined where the item existed between Events.  That is what makes the solution complex, you need to somehow connect an Event to the next related Event to give a start/end date-range for the Inspection to fall into.

You should add additional filtering-criteria to reduce data volume returned by the CTE (Events) and/or Inspections.  Right now, my solution has no filtering, so if both tables have many records then the query may take a while.  I suspect you could write the query without using a CTE, but I'm not sure if that would improve either readability or performance.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.