HAVING / Where time count / excluding any instance

[Rebecca]

Greetings this almost New Year's Eve,

I have a set of data with open and close dates (for one person). I want to
calculate the duration the person was open. But I can't do [close] - [open]
because some of the closures are errors--you can identify an error if the
next open date is the next month. For example, 5/16/2000 closures is an
error--because reopened 6/27/2000. So really, I want to subtract 1/1/2000
date from final closure of that period--which is 4/25/02

Open Close
2/15/1990 4/20/1992
8/1/1995 11/12/1995
1/1/2000 5/16/2000
6/27/2000 12/7/2001
1/4/2002 4/25/2002
3/14/2004 10/17/2004
6/4/2005 11/30/2005

Any thoughts on how to do this?
Here's what I've tried:

1) labeling closures as "bad" or "good" -- if "open date - 30 days < closure
date", then label closure date as "bad". Then I have an array--
5/16/2000 good
5/16/2000 good
5/16/2000 bad ---showing the one time where it closed & reopened
5/16/2000 good

and I'd need to then do a query eliminating a date that has _any_ "bad"
labels (I just submitted a question about this, since my HAVING clause isn't
working properly)
and final step -- subtract open date from smallest close date that is "good".

Any thoughts on a faster/better way to do this? (such as saying "if there's
_any_ open date that's up to 30 days > than the close date, then don't use
that close date in the time duration calculation.

Thank you.

 

[KenSheridan via AccessMonster.com]

Rebecca:

Your post is a little inconsistent as on the one hand you say that the errors
are where "the next open date is the next month", but on the other hand you
say "if there's any open date that's up to 30 days > than the close date",
which is not quite the same (and would make your 16 May 2000 close date
legitimate). So, looking at your sample data it looks like the former is the
case, and I've worked on that basis. You should be able to get the pairings
of all open dates and their legitimate close dates with:

SELECT Open,
(SELECT MIN(Close)
FROM RebeccasTable AS RT2
WHERE (RT2.Close > RT1.Open
AND DATEDIFF("m", RT2.Close,
(SELECT MIN(Open)
FROM RebeccasTable AS RT3
WHERE Rt3.Open > Rt2.Close))>1)
OR Close =
(SELECT MAX(Close)
FROM RebeccasTable))
AS FinalClose
FROM RebeccasTable AS RT1;

You should then be able to eliminate the spurious rows from this query's
result set, i.e. all those with the same FinalClose date, but with Open dates
later than the first one, by basing another query on the above query:

SELECT MIN(qryRebecca.Open) AS Open,
FinalClose
FROM qryRebecca
GROUP BY FinalClose;

If the criterion is in fact 30 or less days, rather than 'in the next month',
then change the first query to:

SELECT Open,
(SELECT MIN(Close)
FROM RebeccasTable AS RT2
WHERE (RT2.Close > RT1.Open
AND DATEDIFF("d", RT2.Close,
(SELECT MIN(Open)
FROM RebeccasTable AS RT3
WHERE RT3.Open > RT2.Close))>30)
OR Close =
(SELECT MAX(Close)
FROM RebeccasTable))
AS FinalClose
FROM RebeccasTable AS RT1;

Ken Sheridan
Stafford, England

Greetings this almost New Year's Eve,

I have a set of data with open and close dates (for one person). I want to
calculate the duration the person was open. But I can't do [close] - [open]
because some of the closures are errors--you can identify an error if the
next open date is the next month. For example, 5/16/2000 closures is an
error--because reopened 6/27/2000. So really, I want to subtract 1/1/2000
date from final closure of that period--which is 4/25/02

Open Close
2/15/1990 4/20/1992
8/1/1995 11/12/1995
1/1/2000 5/16/2000
6/27/2000 12/7/2001
1/4/2002 4/25/2002
3/14/2004 10/17/2004
6/4/2005 11/30/2005

Any thoughts on how to do this?
Here's what I've tried:

1) labeling closures as "bad" or "good" -- if "open date - 30 days < closure
date", then label closure date as "bad". Then I have an array--
5/16/2000 good
5/16/2000 good
5/16/2000 bad ---showing the one time where it closed & reopened
5/16/2000 good

and I'd need to then do a query eliminating a date that has _any_ "bad"
labels (I just submitted a question about this, since my HAVING clause isn't
working properly)
and final step -- subtract open date from smallest close date that is "good".

Any thoughts on a faster/better way to do this? (such as saying "if there's
_any_ open date that's up to 30 days > than the close date, then don't use
that close date in the time duration calculation.

Thank you.