Re: Need help generating the proper SQL query
From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Fri, 10 Apr 2009 18:32:15 -0700 (PDT)
Message-ID: <0f6f198f-4021-4b40-a12c-99f0a646c32f_at_e5g2000vbe.googlegroups.com>
On Apr 10, 4:17 pm, "hilljm1..._at_gmail.com" <hilljm1..._at_gmail.com> wrote:
> 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
Date: Fri, 10 Apr 2009 18:32:15 -0700 (PDT)
Message-ID: <0f6f198f-4021-4b40-a12c-99f0a646c32f_at_e5g2000vbe.googlegroups.com>
On Apr 10, 4:17 pm, "hilljm1..._at_gmail.com" <hilljm1..._at_gmail.com> wrote:
> 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
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 (usetable 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.
Received on Fri Apr 10 2009 - 20:32:15 CDT