Re: Identifying trends

"John Winterbottom" <john_at_assaynet.com> wrote in message news:<PGw37.37385$gb6.4303039_at_news20.bellglobal.com>...
> Apologies in advance if this question is innapropriate here. I am looking
> for a pure SQL solution to
> a problem.
>
> The problem is common in quality control. We have a process from which we
> take
> a sample every day and run a
> test. We wish to identify regions in which 4 out of 5 successive test
> results lie on an increasing trend, (e.g. the following sequence
>
> 3, 5, 2, 6, 8
>
> would be flagged.
>
> However, this sequence:
>
> 3, 5, 2, 4, 8
>
> would not, because the two data points on either side of the invalid one,
> (i.e. 5 and 4) should be increasing..
>
> In our table, test_id is a unique, incrementing integer that represents the
> sequential id number for each test.:
>
> ============================================
> CREATE TABLE test_results
> (
> test_id INT PRIMARY KEY,
> test_value DECIMAL(9,5)
> )
> GO
> INSERT INTO test_results (test_id, test_value) VALUES (1, 2)
> INSERT INTO test_results (test_id, test_value) VALUES (2, 3)
> INSERT INTO test_results (test_id, test_value) VALUES (3, 1)
> INSERT INTO test_results (test_id, test_value) VALUES (4, 4)
> INSERT INTO test_results (test_id, test_value) VALUES (5, 5)
> INSERT INTO test_results (test_id, test_value) VALUES (6, 2)
> INSERT INTO test_results (test_id, test_value) VALUES (7, 2)
> INSERT INTO test_results (test_id, test_value) VALUES (8, 3)
> INSERT INTO test_results (test_id, test_value) VALUES (9, 1)
> INSERT INTO test_results (test_id, test_value) VALUES (10, 2)
> INSERT INTO test_results (test_id, test_value) VALUES (11, 3)
> GO
> =========================================================
>
> The correct solution here would be:
>
> test_id test_id
> ----------- -----------
> 1 5
>

SELECT t1.test_id
FROM test_results t1, test_results t2, test_results t3, test_results t4, test_results t5

where t1.test_id+1 = t2.test_id and t2.test_id+1 = t3.test_id and t3.test_id+1 = t4.test_id and t4.test_id+1 = t5.test_id

and (t1.test_value <= t2.test_value and t2.test_value <= t3.test_value and t3.test_value <= t4.test_value
or t1.test_value <= t2.test_value and t2.test_value <= t3.test_value and t3.test_value <= t5.test_value
or t1.test_value <= t2.test_value and t2.test_value <= t4.test_value and t4.test_value <= t5.test_value
or t1.test_value <= t3.test_value and t3.test_value <= t4.test_value and t4.test_value <= t5.test_value
or t2.test_value <= t3.test_value and t3.test_value <= t4.test_value and t4.test_value <= t5.test_value) Received on Sun Jul 22 2001 - 01:35:06 CEST