Solved

T-SQL Inspects at a Locaton

Posted on 2014-10-08
6
84 Views
Last Modified: 2014-10-15
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
0
Comment
Question by:phil916
  • 3
  • 2
6 Comments
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40368586
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
 

Author Comment

by:phil916
ID: 40368838
OK but what happens if there are non location events, or more than one location events in the sane day?
0
 
LVL 11

Accepted Solution

by:
John_Vidmar earned 500 total points
ID: 40369613
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:phil916
ID: 40369968
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
 

Author Comment

by:phil916
ID: 40370516
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
 
LVL 11

Expert Comment

by:John_Vidmar
ID: 40371829
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

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now