I have a table that stores machine test results in the following form:

MachineId, TestDate, TestType, Pass_Fail, ReadingsCode

where

MachineId is the id of the machine
TestDate is the date the machine was tested
TestType is either I for Initial Test or R for retest
Pass_Fail is either P for Pass or F for Fail
ReadingsCode is the result code of the test

Example Data:

102923866, 20090227, I, F, A
102923866, 20090227, R, P, A
102923866, 20090305, I, F, B
102923866, 20090305, R, P, A
105616535, 20090213, I, P, C
105616535, 20090214, I, F, E
105616535, 20090214, R, F, G
105616535, 20090217, R, F, E
105616535, 20090217, R, P, B
105617235, 20090321, I, P, X
105617233, 20090121, I, P, S

My boss wants a query to show a match / no match of the ReadingsCode
field when the value changes for each retest of a failed initial test.

Output of the query:

MachineId, Initial Test Date, Retest Date, Match, NoMatch

For example, the above table would generate the following output:

102923866, 20090227, 20090227, 1, 0
102923866, 20090305, 20090305, 0, 1
105616535, 20090214, 20090214, 0, 1
105616535, 20090214, 20090217, 1, 0
105616535, 20090214, 20090217, 0, 1

Anyone have any idea how to create this query?

Thanks in advance!!!

Jeremy

On Apr 10, 4:17*pm, "hilljm1... (AT) gmail (DOT) com" <hilljm1... (AT) gmail (DOT) com>
wrote:
This appears to be an interesting problem.

What have you tried so far? When posting requests for assistance in
this group, it is usually a good idea to show:
* What you have tried so far.
* The Oracle version that you are using.
* The DDL and DML to create the test table with the test data (use
table names such as T1, T2, T3, etc.).

You might take a look at the analytical functions LAG and LEAD, which
permit examining the data contained on the previous rows or next rows
when sorted in the specified order. Those functions would help you
avoid having to perform a self-join on the table containing the data.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.