Solved

Max-Min Data Series

Posted on 2011-02-10
7
420 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
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 119

Accepted Solution

by:
Rey Obrero 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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 119

Expert Comment

by:Rey Obrero
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
excel connection properties parameters grayed out 5 25
File not loading into PowerPivot 4 9
Excel callender with date slider 5 25
access 7 12
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

914 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

18 Experts available now in Live!

Get 1:1 Help Now