Menu
I'm wondering if this is possible. I am trying to identify records where there is a pattern in the data.
Eg a table with fields ID, DATA_DTE (only daily records), SPEED.
I would like to identify when the field SPEED drops by at least 4 and holds 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 to have a SQL query return the ID. Eg "1" in the above eg.
Does anyone know how I can setup my SQL query to return these IDs which match a pattern like this? I'll be running it in ms access 2003.
856
SQL pattern matching allows you to search for patterns in data if you don't know the exact word or phrase you are seeking. This kind of SQL query uses wildcard characters to match a pattern, rather than specifying it exactly. For example, you can use the wildcard "C%" to match any string beginning with a capital C.
LIKE clause is used to perform the pattern matching task in SQL. A WHERE clause is generally preceded by a LIKE clause in an SQL query.
Using Regular Expressions With grep You can also use the grep command to search for targets that are defined as patterns by using regular expressions.
I don't know ms access, but it might 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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
