Counting consecutive dates

[RussCRM]

I work for a small homeless shelter. I have a table called
"Services"
that contains records with a ServiceID, GuestID, ServiceDate, and
ServiceType. Such as:
ServiceID (key) GuestID ServicesDate ServicesType
1875 4572 02/01/08 Men's Dorm
1877 9762 02/02/08 Men's Dorm
1892 9762 02/03/08 Men's Dorm
We also have a table called "Guest" with each guest's name and other
information.
For various reasons, we keep an ongoing count of how many nights in a
row a guest has stayed.
To count the total number of nights for a guest, we usually do a
dcount type of thing on ServiceID.
Anyone have any ideas how I could get a count of the number of days
in
a row a guest has stayed?

I have these two queries as suggested:

Query 1:qryCountPrep

SELECT a.ServicesType, a.ServicesID, a.ServicesLink, a.ServicesDate,
COUNT(*) AS Rank
FROM Services AS a INNER JOIN Services AS b ON
(a.ServicesDate>=b.ServicesDate) AND (a.ServicesLink=b.ServicesLink)
GROUP BY a.ServicesType, a.ServicesID, a.ServicesLink, a.ServicesDate;


Query 2:qryCount
SELECT a.ServicesID, Min(a.ServicesDate) AS StartDate,
Max(a.ServicesDate) AS LastDate, Max(a.ServicesDate)-
Min(a.ServicesDate)+1 AS NumberOfDays
FROM qryConsecutiveDateCountPrep2 AS a
WHERE ServicesType="Men's Dorm"
GROUP BY a.ServicesID, a.ServicesDate-a.Rank;

When I run them, I get a count of the number of days between the date
the guest first stayed and the last date the guest stayed. So, if a
guy stayed on February 1st for the first time and again on the 16th,
17th, and 18th. I would get "18" as the total number of days. But, I
want the total number of consecutive days prior to today. So, in this
case, I would be looking for "3" (assuming today is the 19th).

If this isn't possible, how would I at least get the number of stays
for the current month.

 

[Michel Walsh]

It seems something is wrong with your data.


With data:


SomeDates Date
2008.02.01
2008.02.17
2008.02.18
2008.02.19



the first query

SELECT a.Date, COUNT(*) AS rank
FROM SomeDates AS a INNER JOIN SomeDates AS b ON a.date>= b.date
GROUP BY a.date

returns:


RankSomeDate Date rank
2008.02.01 1
2008.02.17 2
2008.02.18 3
2008.02.19 4



and the last query


SELECT Min(RankSomeDate.Date) AS MinOfDate,
Max(RankSomeDate.Date) AS MaxOfDate,
Max(date)-Min(date)+1 AS lengthOfStay
FROM RankSomeDate
GROUP BY date-rank;


returns:


Query82
Expr1000 Expr1001 lengthOfStay
2008.02.01 2008.02.01 1
2008.02.17 2008.02.19 3





which is exactly what you want. So, your second query should also return the
length of Stay, per period, so you should get a 1 and a 3, and not 18 as you
mentioned.

On the other hand, if you only want the MAXIMUM stay, make another query
over the last one, to get that information.


If your data does not contain information for tomorrow or days after, the
query should not take those into account either. Sure, you can add a WHERE
clause which will restrict the dates to consider.




Vanderghast, Access MVP

 

[RussCRM]

I'm getting there. Now I what you're referring to above. I can even
get ranges for each guest, but I end up with several date ranges/
counts for each guest. However, now I want to isolate them by each
guest (GuestID from the same table) and only ranges ending in today.
That should give me a count of the number of nights each guest has
stayed in a row up until today...?

 

[Michel Walsh]

If the last query you already have is called qLast, then, make a new query
like:


SELECT *
FROM qLast
WHERE MaxOfDate = Date()


which assumed qLast field for the last date of each range is called
MaxOfDate.


Alternatively, edit qLast to add:

HAVING MaxOfDate = Date()


but that condemns qLast to display only that information, from now on. That
is why writing a new query may be a better alternative.



Hoping it may help,
Vanderghast, Access MVP

 

[RussCRM]

Thank you so much for your help! This gives me what I need! Well,
almost...

The count is right! Now I just need to be able to match up the count
to the guest...which seems to be another matter entirely...

 

[RussCRM]

Nevermind! Got it!

Thanks again for all your help!