Confusing Query based on Dates

[Joe Cilinceon]

I can't seem to grasp what I need to do with this.

I have a Table (LEASES) that has a UnitNo (storage unit), StartDate and
EndDate. Now the EndDate will be Null if the unit is rented, StartDate is
when it was rented. Now I want to query the LEASE table to get a count of
units rented on the last day of each month. What I'm looking for is the
occupancy at the End of the Month only.

 

[Allen Browne]

SELECT Count(*) AS HowMany
FROM Leases
WHERE #11/30/2005# Between StartDate And Nz([EndDate], #1/1/2299#);

 

[fredg]

I can't seem to grasp what I need to do with this.

I have a Table (LEASES) that has a UnitNo (storage unit), StartDate and
EndDate. Now the EndDate will be Null if the unit is rented, StartDate is
when it was rented. Now I want to query the LEASE table to get a count of
units rented on the last day of each month. What I'm looking for is the
occupancy at the End of the Month only.

If all you need are the Null EndDates for the current month, run the
query at the end of the current month.

SELECT Sum(IIf(IsNull([EndDate]),1,0)) AS Rented
FROM Leases;

 

[Joe Cilinceon]

I can't seem to grasp what I need to do with this.

I have a Table (LEASES) that has a UnitNo (storage unit), StartDate
and EndDate. Now the EndDate will be Null if the unit is rented,
StartDate is when it was rented. Now I want to query the LEASE table
to get a count of units rented on the last day of each month. What
I'm looking for is the occupancy at the End of the Month only.

If all you need are the Null EndDates for the current month, run the
query at the end of the current month.

SELECT Sum(IIf(IsNull([EndDate]),1,0)) AS Rented
FROM Leases;

Yes that I had figured out. Actually I want to be able to query and get the
# of units rented for last day of any given month. I could also use a movin
/ moveout count over a period. We often will rent a unit in the beginning of
a month and re rent it before the end of the same month. Right now the max I
could go back is 1/1/05 as this app didn't existance prior to that. This
will eventually become part of an End of Year report.

 

[Joe Cilinceon]

Wow! that worked great thanks Allen. Ok I think I understand what you did.
You're setting the EndDate if Null to any date in the future. and the
#11/30/05# would be the last day of this month. The last day of any other
month would give the occ for that month. Great just what I needed.

SELECT Count(*) AS HowMany
FROM Leases
WHERE #11/30/2005# Between StartDate And Nz([EndDate], #1/1/2299#);

I can't seem to grasp what I need to do with this.

I have a Table (LEASES) that has a UnitNo (storage unit), StartDate
and EndDate. Now the EndDate will be Null if the unit is rented,
StartDate is when it was rented. Now I want to query the LEASE table
to get a count of units rented on the last day of each month. What
I'm looking for is the occupancy at the End of the Month only.

 

[Allen Browne]

To achieve a count for each month, you would need a table of dates,
containing just the first of each month. Name the table (say) tblDate, with
a date/time field named TheDate, marked as primary key. Then enter records:
1/1/2005
1/2/2005
1/3/...
This guarantees you a result for every month, even if there were no
transactions in that month.

Then use this table as the source for a query, and use a series of
subqueries to get the desired count from your Leases table:

SELECT tblDate.TheDate,
(SELECT Count("*") FROM Leases
WHERE tblDate.TheDate Between Leases.StartDate And
Nz(Leases.EndDate, #1/1/2299#)) AS StartOfMonthCount,
(SELECT Count("*") FROM Leases
WHERE Leases.StartDate Between tblDate.TheDate And
DateAdd("m",1,[tblDate].[TheDate])-1) AS MovedIn,
(SELECT Count("*") FROM Leases
WHERE Leases.EndDate Between tblDate.TheDate And
DateAdd("m",1,[tblDate].[TheDate])-1) AS MovedOut
FROM tblDate
ORDER BY tblDate.TheDate;

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

Notes:
a) Results will be read-only when you use a subqeury in the SELECT clause.
b) Reports can barf with a "multi-level group by error" if you try to group
or sort by the results of the subqueries.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I can't seem to grasp what I need to do with this.

I have a Table (LEASES) that has a UnitNo (storage unit), StartDate
and EndDate. Now the EndDate will be Null if the unit is rented,
StartDate is when it was rented. Now I want to query the LEASE table
to get a count of units rented on the last day of each month. What
I'm looking for is the occupancy at the End of the Month only.

If all you need are the Null EndDates for the current month, run the
query at the end of the current month.

SELECT Sum(IIf(IsNull([EndDate]),1,0)) AS Rented
FROM Leases;

Yes that I had figured out. Actually I want to be able to query and get
the # of units rented for last day of any given month. I could also use a
movin / moveout count over a period. We often will rent a unit in the
beginning of a month and re rent it before the end of the same month.
Right now the max I could go back is 1/1/05 as this app didn't existance
prior to that. This will eventually become part of an End of Year report.

 

[Joe Cilinceon]

Thanks for all of this Allen, this project is finally looking like it might
get there. g

To achieve a count for each month, you would need a table of dates,
containing just the first of each month. Name the table (say)
tblDate, with a date/time field named TheDate, marked as primary key.
Then enter records: 1/1/2005
1/2/2005
1/3/...
This guarantees you a result for every month, even if there were no
transactions in that month.

Then use this table as the source for a query, and use a series of
subqueries to get the desired count from your Leases table:

SELECT tblDate.TheDate,
(SELECT Count("*") FROM Leases
WHERE tblDate.TheDate Between Leases.StartDate And
Nz(Leases.EndDate, #1/1/2299#)) AS StartOfMonthCount,
(SELECT Count("*") FROM Leases
WHERE Leases.StartDate Between tblDate.TheDate And
DateAdd("m",1,[tblDate].[TheDate])-1) AS MovedIn,
(SELECT Count("*") FROM Leases
WHERE Leases.EndDate Between tblDate.TheDate And
DateAdd("m",1,[tblDate].[TheDate])-1) AS MovedOut
FROM tblDate
ORDER BY tblDate.TheDate;

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

Notes:
a) Results will be read-only when you use a subqeury in the SELECT
clause. b) Reports can barf with a "multi-level group by error" if
you try to group or sort by the results of the subqueries.

On Sun, 6 Nov 2005 19:53:24 -0500, Joe Cilinceon wrote:

I can't seem to grasp what I need to do with this.

I have a Table (LEASES) that has a UnitNo (storage unit), StartDate
and EndDate. Now the EndDate will be Null if the unit is rented,
StartDate is when it was rented. Now I want to query the LEASE
table to get a count of units rented on the last day of each
month. What I'm looking for is the occupancy at the End of the
Month only.

If all you need are the Null EndDates for the current month, run the
query at the end of the current month.

SELECT Sum(IIf(IsNull([EndDate]),1,0)) AS Rented
FROM Leases;

Yes that I had figured out. Actually I want to be able to query and
get the # of units rented for last day of any given month. I could
also use a movin / moveout count over a period. We often will rent a
unit in the beginning of a month and re rent it before the end of
the same month. Right now the max I could go back is 1/1/05 as this
app didn't existance prior to that. This will eventually become
part of an End of Year report.