Six Months Over Threshhold

  • Thread starter Thread starter Ian Smith
  • Start date Start date
I

Ian Smith

Not here is an interesting query requirement...

Given a table of monthly occupancy for a series of
properties, I need to determine which properties have ever
had a string of six consecutive months with 90% or better
occupancy.

Anyone?
 
In your query you could use DateDiff. The example I have
identified below, will provide you with a day count. Once
you have a day count you can have it provide total records
=180 (days). Then you can find the >= 90% on the
Click to expand...
occupancy.

DateDiff("d",[Date_field],Now())

I hope this helps,
Scott
 
Given two tables described below:

--tblProperties
PropertyID Property
1 Property A
2 Property B
3 Property C

--tblOccupancy
PropertyID MonthYear Occupancy
1 1/1/2000 83.00%
1 2/1/2000 84.00%
1 3/1/2000 85.00%
1 4/1/2000 90.00%
1 5/1/2000 90.00%
1 6/1/2000 90.00%
1 7/1/2000 90.00%
1 8/1/2000 90.00%
1 9/1/2000 90.00%
1 10/1/2000 93.00%
1 11/1/2000 70.00%
1 12/1/2000 80.00%
2 1/1/2000 83.00%
2 2/1/2000 84.00%
2 3/1/2000 85.00%
2 4/1/2000 85.00%
2 5/1/2000 85.00%
2 6/1/2000 85.00%
2 7/1/2000 85.00%
2 8/1/2000 85.00%
2 9/1/2000 85.00%
2 10/1/2000 85.00%
2 11/1/2000 70.00%
2 12/1/2000 80.00%
3 1/1/2000 83.00%
3 2/1/2000 84.00%
3 3/1/2000 85.00%
3 4/1/2000 90.00%
3 5/1/2000 90.00%
3 6/1/2000 90.00%
3 7/1/2000 90.00%
3 8/1/2000 83.00%
3 9/1/2000 90.00%
3 10/1/2000 93.00%
3 11/1/2000 70.00%
3 12/1/2000 80.00%


Construct these queries:

Query 1: Remove months that do not qualify (those less
than 90% occupancy)

--qryOccupancyHigh
SELECT tblOccupancy.PropertyID, tblOccupancy.MonthYear,
tblOccupancy.Occupancy
FROM tblOccupancy
WHERE (((tblOccupancy.Occupancy)>=0.9))


Query 2: Give me a list of properties and their months

--qryPropertyMonths
SELECT tblOccupancy.PropertyID, tblOccupancy.MonthYear
FROM tblOccupancy


Query 3: Identify those months for a given property where
at least 6 months of high occupancy exist (note the date
range where I ask for 6 months of data, then expect at
least 6 results).

--qryDontIUsuallyGetPaidForThis
SELECT MO.PropertyID, MO.MonthYear, (SELECT OH.PropertyID
FROM qryOccupancyHigh AS OH
WHERE OH.PropertyID = MO.PropertyID
AND OH.MonthYear >= MO.MonthYear
AND OH.MonthYear < DATEADD("m", 6, MO.MonthYear)
GROUP BY OH.PropertyID
HAVING Count(*) > 5) AS SUB
FROM qryPropertyMonths AS MO;

Yields the following:
PropertyID MonthYear SUB
1 1/1/2000
1 2/1/2000
1 3/1/2000
1 4/1/2000 1 <-- not null indicates hit
1 5/1/2000 1 <-- two months now at 6+ mo
1 6/1/2000
1 7/1/2000
1 8/1/2000
1 9/1/2000
1 10/1/2000
1 11/1/2000
1 12/1/2000
2 1/1/2000
2 2/1/2000
2 3/1/2000
2 4/1/2000
2 5/1/2000
2 6/1/2000
2 7/1/2000
2 8/1/2000
2 9/1/2000
2 10/1/2000
2 11/1/2000
2 12/1/2000
3 1/1/2000
3 2/1/2000
3 3/1/2000
3 4/1/2000
3 5/1/2000
3 6/1/2000
3 7/1/2000
3 8/1/2000
3 9/1/2000
3 10/1/2000
3 11/1/2000
3 12/1/2000


Query 4: Pack out the the ugly and just plain wasteful.

--qryPayDirt
SELECT qryDontIUsuallyGetPaidForThis.PropertyID
FROM qryDontIUsuallyGetPaidForThis
WHERE (((qryDontIUsuallyGetPaidForThis.SUB) Is Not Null))
GROUP BY qryDontIUsuallyGetPaidForThis.PropertyID


Yields your answer:

PropertyID
1


I'm sure there are other ways of doing this. This is just
the one that came to my mind. And hey, I even tested it!

David Atkins, MCP
 
Back
Top