Consecutive Records

K

Keith

I have a database which is completed at the end of every day. Without going
into every field, the main ones that are causing me a problem are:-

I wish to record a Yes/No (or it could be a 1 - 0) to denote whether a
target has been reached or not. What I then want to do is to make a count of
how many consecutive days Yes and how many consecutive days of No so that
the highest number of consecutive days of hitting or missing the target can
be seen and which dates they occurred on.

So the summary I would like to see is something like:-

Highest Consecutive Hits = 8 this occurred on 18th May 2005

or

Highest Consecutive Hits = 8 this occurred 3 times, on 18th May 2005, 21st
July 2005, 7th November 2005

and the same for conescutive None Hits.

Any ideas please?

Thanks

Keith
 
T

Tomislav ©ereg

Keith said:
(...) make a count of
how many consecutive days Yes and how many consecutive days of No (...)
Click to expand...

Here is a thought - this is not a solution, but an idea, just to see if
I got you right.

If you have a table like this:

(note that I use numbers for dates, but this doesn't matter, you can use
dates instead)
T := (day, target)
1 Y
2 Y
3 N
4 Y
5 Y
6 Y
7 N
8 N
9 Y
10 Y

would something like this be helpful:

Query := (x,day,target)
2 1 Y
2 2 Y
3 3 N
6 4 Y
6 5 Y
6 6 Y
8 7 N
8 8 N
- 9 Y
- 10 Y

Here is the query.

SELECT
*
, (
SELECT
MAX(T2.day)
FROM
T AS T2
WHERE
T2.day < (
SELECT
MIN(T1.day)
FROM
T AS T1
WHERE
T.day < T1.day AND T1.target <> T.target
)
) AS X
FROM
T

The idea is that you have labeled the "consecutive runs" by using the
most recent date still in the run. If this is a view (or stored query),
you could use GROUP BY to count number of records/days in each run and
output the label of the run with most records. There is a small thing
with this NULL value for the last run (the run may not be over yet), but
this should not be a problem, as it means "this is still happening".

The "counting query" might look like this:

SELECT X, COUNT(*)
FROM Q
GROUP BY X;

whre Q is the name of stored query.

It will produce this:

X count
- 2
2 2
3 1
6 3
8 2

On day "6" the longest run has finished. If NULL is label of the longest
run, you might output something like:
Highest Consecutive Hits = X, and still counting!!! :)
 
Top