Max-Min Data Series

I have couple of clients where their shelter entries/exits that are recorded on a daily bases. The challenge is that I need to calculate length of stay (Max-Min dates) and length of time between shelter visits. I know how to calculate length of stay and time between visits if Client shelter entries are formatted like:

Client   Program     Entry Date    Exit Date
18         Shelter A    8/18/2010     8/22/2010
18         Shelter A    9/2/2010       9/6/2010
19         Shelter A    9/3/2010       9/8/2010

But I’m not sure how to even approach data when drop looks like:

Client   Program     Entry Date    Exit Date
18         Shelter A    8/18/2010     8/19/2010
18         Shelter A    8/19/2010     8/20/2010
18         Shelter A    8/20/2010     8/21/2010
18         Shelter A    8/21/2010     8/22/2010
18         Shelter A    9/2/2010       9/3/2010
18         Shelter A    9/3/2010       9/4/2010
18         Shelter A    9/4/2010       9/5/2010
18         Shelter A    9/5/2010       9/6/2010
19         Shelter A    9/3/2010       9/4/2010
19         Shelter A    9/4/2010       9/5/2010
19         Shelter A    9/6/2010       9/7/2010
19         Shelter A    9/7/2010       9/8/2010

Is there a way to convert date series to max min date by client? Any suggestions are appreciated.

Thanks
jbakerstullAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
try this query


SELECT b.Client, b.Program, Min(b.[Entry Date]) AS [MinOfEntry Date], Max(b.[Exit Date]) AS [MaxOfExit Date]
FROM Visit AS b
GROUP BY b.Client, b.Program, Format([Entry Date],"yyyymm"), Format([Exit Date],"yyyymm");
0
 
HainKurtSr. System AnalystCommented:
your sample is not clear... do you mean this?

Client   Program     Entry Date    Exit Date
18         Shelter A    8/18/2010     8/22/2010
19         Shelter A    9/3/2010       9/8/2010

0
 
jbakerstullAuthor Commented:
Yes, but client 18 left on 8/22/2010 and then would reappeared on 9/2/2010 for a length of time:

So client 18 would have two separate shelter visits:

18         Shelter A    8/18/2010     8/22/2010
18         Shelter A    9/2/2010       9/6/2010
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
jbakerstullAuthor Commented:
I rank Client Id by using Entry Date to create:

Client   Program     Entry Date      Exit Date      Rank
18         Shelter A    8/18/2010     8/19/2010    1
18         Shelter A    8/19/2010     8/20/2010    2
18         Shelter A    8/20/2010     8/21/2010    3
18         Shelter A    8/21/2010     8/22/2010    4
18         Shelter A    9/2/2010       9/3/2010      1
18         Shelter A    9/3/2010       9/4/2010      2
18         Shelter A    9/4/2010       9/5/2010      3
18         Shelter A    9/5/2010       9/6/2010      4
19         Shelter A    9/3/2010       9/4/2010      1
19         Shelter A    9/4/2010       9/5/2010      2
19         Shelter A    9/6/2010       9/7/2010      3
19         Shelter A    9/7/2010       9/8/2010      4

and then concatenate Client & Rank to create a unique key and then use min, max to identify entries and exits. But I stuck on how many times client should be listed without manually (using countif) listing many times the client appears..  
0
 
Rey Obrero (Capricorn1)Commented:
try the query i posted
0
 
jbakerstullAuthor Commented:
I work off an internet server so its a datapull into excel. It will take me a few minutes to set up a test database..  Please don' t go anywhere.
0
 
jbakerstullAuthor Commented:
Tested.. works.. thank you sir.
DateTest.accdb
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.