Solved

# Max-Min Data Series

Posted on 2011-02-10
431 Views
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
0
Question by:jbakerstull
• 4
• 2

LVL 51

Expert Comment

ID: 34865846
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

Author Comment

ID: 34865982
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

LVL 120

Accepted Solution

Rey Obrero (Capricorn1) earned 500 total points
ID: 34866203
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

Author Comment

ID: 34866240
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

LVL 120

Expert Comment

ID: 34866264
try the query i posted
0

Author Comment

ID: 34866372
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

Author Comment

ID: 34866463
Tested.. works.. thank you sir.
DateTest.accdb
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.