Showing only records that are different from those preceding them.

  • Thread starter Thread starter dandc0711
  • Start date Start date
D

dandc0711

I have a table that generates automatically from a constant data feed from a
crystal grower. The pertinant data generated is the serial ID (RunID),
Datetime of the data (DATETIME), and what process phase its in (PROCESS).

This data comes across once a minute and each run can take up to 3 days.
Every day I have to manually take datetimes from specific points in this
process.

I need to make a Query that sorts by RunID and DATETIME and only displays
the fields immediatly before and after a process change. So for example if
the table looked like this:

RUNID | DATETIME | PROCESS
0001 1/1/09 1201 Neck
0001 1/1/09 1202 Neck
0001 1/1/09 1203 Neck
0001 1/1/09 1204 Neck
0001 1/1/09 1205 Neck
0001 1/1/09 1206 Neck
0001 1/1/09 1207 Body
0001 1/1/09 1208 Body
0001 1/1/09 1209 Body
0001 1/1/09 1210 Body
0001 1/1/09 1211 Tail
0001 1/1/09 1212 Tail
0001 1/1/09 1213 Tail
0001 1/1/09 1214 Body

I need the query to produce a result like this:


RUNID | DATETIME | PROCESS
0001 1/1/09 1201 Neck
0001 1/1/09 1206 Neck
0001 1/1/09 1207 Body
0001 1/1/09 1210 Body
0001 1/1/09 1211 Tail
0001 1/1/09 1213 Tail
0001 1/1/09 1214 Body

With the goal being to strip out all the middle values of each process group
and present a much smaller group to choose from since I"m only concerned
about the min and max date times.

I tried using a Group by but if I grouped by PROCESS then it will disregard
the DATETIME sorting and grab all instances of the process throughout the
table and I would loose the ability to see when the process changed multiple
times throughout the same run.
 
This MIGHT work depending on whether or not the Time is accurate to the minute
(no seconds).

SELECT A.RunID, A.DateTime
FROM Table as A LEFT JOIN Table As B
ON A.RUNID = B.RUNID
AND A.DateTime = DateAdd("n",-1,B.DateTime)
WHERE A.Process <> B.Process OR B.RunID is Null

If the time has the seconds also you could try changing the join to
AND Format(A.DateTime,"yyyymmddhhnn") =
Format(DateAdd("n",-1,B.DateTime),"yyyymmddhhnn")

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top