How to identify or recoginize a pattern in data with SQL

Question

I am wondering if this is possible. I am trying to identify records where there is a pattern in the data.

For example, a table with fields ID , DATA_DTE (daily entries only), SPEED .

I would like to determine when the SPEED field falls by at least 4 and is held for at least 3 consecutive days in a row

**ID**..**DATA_DTE**...**SPEED** 1........Jan-1............8 1........Jan-2............9 1........Jan-3............4 1........Jan-4............4 1........Jan-5............4 1........Jan-6............7 1........Jan-7............8 1........Jan-8............9 

From the above, I basically want the SQL query to return an identifier. For example, “1” in the above example.

Does anyone know how I can customize my SQL query to return these identifiers matching the pattern? I will run it in MS Access 2003.

Answer 1

I don't know ms access, but he could do the job:

 SELECT DISTINCT id FROM   data AS d1 INNER JOIN data AS d2 ON d1.id = d2.id AND DateAdd("d",+1,d1.data_dte) = d2.data_dte INNER JOIN data AS d3 ON d1.id = d3.id AND DateAdd("d",+2,d1.data_dte) = d3.data_dte INNER JOIN data AS d4 ON d1.id = d4.id AND DateAdd("d",+3,d1.date_dte) = d4.data_dte WHERE d1.speed - d2.speed >= 4 AND d1.speed - d3.speed >= 4 AND d1.speed - d4.speed >= 4