?
Solved

Max-Min Data Series

Posted on 2011-02-10
7
Medium Priority
?
453 Views
Last Modified: 2012-05-11
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
Comment
Question by:jbakerstull
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 57

Expert Comment

by:HainKurt
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

by:jbakerstull
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

by:
Rey Obrero (Capricorn1) earned 2000 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

Author Comment

by:jbakerstull
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

by:Rey Obrero (Capricorn1)
ID: 34866264
try the query i posted
0
 

Author Comment

by:jbakerstull
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

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

Featured Post

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

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

This article describes a serious pitfall that can happen when deleting shapes using VBA.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question