Need help generating the proper SQL query

From: <hilljm1974_at_gmail.com>
Date: Fri, 10 Apr 2009 13:17:35 -0700 (PDT)
Message-ID: <eafc3105-60cd-4534-9c13-9d0c48be89b9_at_v1g2000prd.googlegroups.com>

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 Received on Fri Apr 10 2009 - 15:17:35 CDT