Identifying trends

From: John Winterbottom <john_at_assaynet.com>
Date: Sat, 21 Jul 2001 23:35:02 GMT
Message-ID: <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

The closest solution I have found, (suggested by Itzik Ben-Gan in one of the sqlserver groups), incorrectly returns the second region as well.:

test_id test_id
----------- -----------

1                5
7                11

This is the solution given by Itzik:

---

SELECT T1.test_id, T2.test_id
FROM test_results AS T1
  JOIN test_results AS T2
    ON T2.test_id - 4 = T1.test_id
WHERE (SELECT COUNT(*)

       FROM test_results AS T3 JOIN test_results AS T4
         ON T3.test_id = T4.test_id + 1
       WHERE T3.test_value > T4.test_value
         AND T3.test_id BETWEEN T1.test_id + 1 AND T2.test_id) = 3
==============================

If anyone can help I would appreciate it. Received on Sun Jul 22 2001 - 01:35:02 CEST