Solved

Max-Min Data Series

Posted on 2011-02-10
7
410 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
  • 4
  • 2
7 Comments
 
LVL 51

Expert Comment

by:HainKurt
Comment Utility
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
Comment Utility
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 119

Accepted Solution

by:
Rey Obrero earned 500 total points
Comment Utility
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:jbakerstull
Comment Utility
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 119

Expert Comment

by:Rey Obrero
Comment Utility
try the query i posted
0
 

Author Comment

by:jbakerstull
Comment Utility
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
Comment Utility
Tested.. works.. thank you sir.
DateTest.accdb
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
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.

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now