Finding the first LOCAL minimum in a time series

[Guest]

I am tracking a variable for different subjects over time. I want to find
the date and value of the FIRST local minimum, NOT the global minimum for
the subject.

Here is some sample data:

ID Value Date
1 10 1/10/03
1 5 1/10/04
1 1 1/10/05
1 0.5 1/10/06
1 2 3/10/06
1 0.1 3/12/06
1 4 3/16/06

2 3 1/10/01
2 1 1/10/02
2 1 3/10/02
2 2 6/10/02.
2 0.2 5/10/03
2 5 6/3/04

I want a query that will give the following results:

ID Value Date
1 0.5 1/10/06
2 1 1/10/02

Any suggestions on how to do this with SQL only or does it require a
recursive script?

 

[Duane Hookom]

What do you mean by "different subjects"? There is no "subject" in your
data.
What do you mean by "FIRST local minimum"? There is no "local" in your data.
Why aren't
1 0.1 3/12/06
2 0.2 5/10/03
returned in your query?

 

[Gary Walter]

Interesting...I wish I didn't have to
go into work....

I think this is like in calculus where
the truly interesting points of an
"messy" function are where the
slope changes (or especially where
the slope is zero).

so you find the "slope" by subtracting
the previous value for a specific ID
from the current value.

then pick out first record for an ID
(min date) that "slope"
goes from negative to zero (or positive).

I'm sorry I don't have time right now
to work thru some "gotchas" but I'd
start with query that subtracts previous
from current.

Actually..it might be better to subtract current
value from next value...

If no one has provided solution tonight
I will work on it.

good luck,

gary

 

[Gary Walter]

one "quick-and-dirty" solution:

I'm going to assume "tblStegman"
is name of your table and field names
do not use reserved words (changed
"Value" to "AValue" and "Date" to "ADate")

first query ("qryStegman1")

SELECT
tblStegman.ID,
tblStegman.AValue,
tblStegman.ADate,
(SELECT TOP 1 t1.AValue
FROM tblStegman As t1
WHERE
t1.ID = tblStegman.ID
AND
t1.ADate > tblStegman.ADate
ORDER BY t1.ADate) AS NextValue
FROM tblStegman
WHERE
((((SELECT TOP 1 t1.AValue
FROM tblStegman As t1
WHERE
t1.ID = tblStegman.ID
AND
t1.ADate > tblStegman.ADate
ORDER BY t1.ADate))>=[AValue]));

final query (based on first query):

SELECT
qS.ID,
qS.AValue,
qS.ADate
FROM qryStegman1 AS qS
WHERE
(((qS.ADate)=
(SELECT
Min(q.ADate)
From qryStegman1 As q
WHERE q.ID = qS.ID)))
GROUP BY qS.ID, qS.AValue, qS.ADate;

good luck,

gary


"Gary Walter"wrote"

 

[Gary Walter]

Well..I'm already "very late," so
here be a "less-dirty" solution:

In the first query we bring 2 instances
of your table, aliasing one as "CurrentData"
and the other as "NextData." Using 2 copies
of a table is acommon way for handling
values from "adjacent rows."

What makes a row from NextData
the adjacent (next) row to a row in
CurrentData?

- the ID's need to match up.
- the ADate for NextData needs
to be the earliest date in the table
that is later than current date.

SELECT
CurrentData.ID,
CurrentData.AValue,
CurrentData.ADate,
NextData.AValue AS NextValue
FROM tblStegman AS CurrentData
INNER JOIN tblStegman AS NextData
ON CurrentData.ID = NextData.ID
WHERE
NextData.ADate =
(SELECT Min(t.Adate)
FROM tblStegman As t
WHERE t.ID = CurrentData.ID
AND
t.ADate > CurrentData.ADate);

from your example data, we'd get:

ID AValue ADate NextValue
1 10 1/10/2003 5
1 5 1/10/2004 1
1 1 1/10/2005 0.5
1 0.5 1/10/2006 2
1 2 3/10/2006 0.1
1 0.1 3/12/2006 4
2 3 1/10/2001 1
2 1 1/10/2002 1
2 1 3/10/2002 2
2 2 6/10/2002 0.2
2 0.2 5/10/2003 5

if we only care about the rows
where next value >= current value
(where "slope" changes), then we
can add that criteria to the query above
(this is all predicated on my actually
understanding what you want...)

SELECT
CurrentData.ID,
CurrentData.AValue,
CurrentData.ADate,
NextData.AValue AS NextValue
FROM tblStegman AS CurrentData
INNER JOIN tblStegman AS NextData
ON CurrentData.ID = NextData.ID
WHERE
(NextData.AValue >= CurrentData.AValue)
AND
(NextData.ADate =
(SELECT Min(t.Adate)
FROM tblStegman As t
WHERE t.ID = CurrentData.ID
AND
t.ADate > CurrentData.ADate));

producing from your example data:

ID AValue ADate NextValue
1 0.5 1/10/2006 2
1 0.1 3/12/2006 4
2 1 1/10/2002 1
2 1 3/10/2002 2
2 0.2 5/10/2003 5

we can save this as "qryMins"
then final query simply picks out
the minimum date for each ID.

SELECT
qM.ID,
qM.AValue,
qM.ADate
FROM qryMins AS qM
WHERE
qM.ADate =
(SELECT MIN(q.ADate)
FROM qryMins As q
WHERE q.ID = qM.ID)
GROUP BY
qM.ID,
qM.AValue,
qM.ADate;

ID AValue ADate
1 0.5 1/10/2006
2 1 1/10/2002

I guess I'm really late now,
but this is less "dirty."

good luck,

gary

 

[Gary Walter]

I don't know where that "group by" came from
(other than me )

this should work just fine thank you
for your final query....

SELECT
qM.ID,
qM.AValue,
qM.ADate
FROM qryMins AS qM
WHERE
qM.ADate =
(SELECT MIN(q.ADate)
FROM qryMins As q
WHERE q.ID = qM.ID);