If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
HAVING / Where time count / excluding any instance
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. |
#2
|
|||
|
|||
HAVING / Where time count / excluding any instance
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 Rebecca wrote: 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. -- Message posted via http://www.accessmonster.com |
Thread Tools | |
Display Modes | |
|
|