days between end of one record and start of another

[Bibi]

I have a rental database. I need to be able to calculate the number of days
a property is vacant. Nothing I have tried has worked. I do not write
code.



My database has a table called Leases

With fields:

Number

Property ID

Lease Begins

Lease Ends


All help appreciated.

 

[John W. Vinson]

I have a rental database. I need to be able to calculate the number of days
a property is vacant. Nothing I have tried has worked. I do not write
code.



My database has a table called Leases

With fields:

Number

Property ID

Lease Begins

Lease Ends


All help appreciated.

So you want to calculate the number of days from one record's Lease Ends
value, to the next record's Lease Begins, for the same property? Not surprised
you're having trouble, this is a tricky one!

Try copying and pasting this query into the SQL view of a new query:

SELECT A.[Property ID], DateDiff("d", [A].[Lease Ends], .[Lease Begins]) AS
DaysVacant
FROM Leases AS A INNER JOIN Leases AS B
WHERE [A].[Lease Ends] = (SELECT Max([C].[Lease Ends]) FROM Leases AS C WHERE
C.[Property ID] = [A].[Property ID] AND [C].[Lease Ends] < .[Lease
Begins]);

Untested air code query - post back with some sample data and the results (or
error message) that you get if it doesn't work.

 

[Bibi]

Thank you so much for responding. I had the error message syntax error in
the FROM statement.

Here's some data:
Number Property Id Lease Begins Lease Ends
1419 1000we 8/10/00 8/10/01
866 1000we 8/17/01 8/10/02
715 1000we 8/11/02 2/28/03
1830 1000we 8/11/02 2/28/03
105 1000we 8/1/08 7/31/09
1423 1014we 8/16/00 8/2/01
110 1014we 8/16/00 8/2/01
2374 1100nw 7/1/04 12/31/04
423 120nh 12/12/96 2/12/02
1641 120nh 12/12/96 2/12/02
933 1212sd 12/15/01 8/10/02
1432 121es 8/13/99 8/31/03
2488 121es 12/21/04 3/1/05
2817 121es 8/20/05 8/15/06
3601 121es 8/21/06 11/27/06
3968 121es 12/6/06 8/20/07
5188 121es 8/15/08 8/10/09
6004 121es 8/11/09 8/10/10
424 121nh 12/12/96 2/12/02
1642 121nh 12/12/96 2/12/02
253 1230tt 7/14/99 6/30/01

Thank you.



--


TIA
Bibi

I have a rental database. I need to be able to calculate the number of days
a property is vacant. Nothing I have tried has worked. I do not write
code.



My database has a table called Leases

With fields:

Number

Property ID

Lease Begins

Lease Ends


All help appreciated.

So you want to calculate the number of days from one record's Lease Ends
value, to the next record's Lease Begins, for the same property? Not surprised
you're having trouble, this is a tricky one!

Try copying and pasting this query into the SQL view of a new query:

SELECT A.[Property ID], DateDiff("d", [A].[Lease Ends], .[Lease Begins]) AS
DaysVacant
FROM Leases AS A INNER JOIN Leases AS B
WHERE [A].[Lease Ends] = (SELECT Max([C].[Lease Ends]) FROM Leases AS C WHERE
C.[Property ID] = [A].[Property ID] AND [C].[Lease Ends] < .[Lease
Begins]);

Untested air code query - post back with some sample data and the results (or
error message) that you get if it doesn't work.

 

[John W. Vinson]

Thank you so much for responding. I had the error message syntax error in
the FROM statement.

Ummm... could you post the actual SQL you're using? I can't see it from here.

 

[Bibi]

Here's the code:


SELECT A.[Property ID], DateDiff("d", [A].[Lease Ends], .[Lease Begins]) AS
DaysVacant
FROM Leases AS A INNER JOIN Leases AS B
WHERE [A].[Lease Ends] = (SELECT Max([C].[Lease Ends]) FROM Leases AS C WHERE
C.[Property ID] = [A].[Property ID] AND [C].[Lease Ends] < .[Lease
Begins]);


Here's some data:
Here's some data:
Number Property Id Lease Begins Lease Ends
1419 1000we 8/10/00 8/10/01
866 1000we 8/17/01 8/10/02
715 1000we 8/11/02 2/28/03
1830 1000we 8/11/02 2/28/03
105 1000we 8/1/08 7/31/09
1423 1014we 8/16/00 8/2/01
110 1014we 8/16/00 8/2/01
2374 1100nw 7/1/04 12/31/04
423 120nh 12/12/96 2/12/02
1641 120nh 12/12/96 2/12/02
933 1212sd 12/15/01 8/10/02
1432 121es 8/13/99 8/31/03
2488 121es 12/21/04 3/1/05
2817 121es 8/20/05 8/15/06
3601 121es 8/21/06 11/27/06
3968 121es 12/6/06 8/20/07
5188 121es 8/15/08 8/10/09
6004 121es 8/11/09 8/10/10
424 121nh 12/12/96 2/12/02
1642 121nh 12/12/96 2/12/02
253 1230tt 7/14/99 6/30/01

Any help would be truly appreciated.
Thank you.

 

[Bibi]

Hi John
Here it is


SELECT A.[Property ID], DateDiff("d", [A].[Lease Ends], .[Lease Begins]) AS
DaysVacant
FROM Leases AS A INNER JOIN Leases AS B
WHERE [A].[Lease Ends] = (SELECT Max([C].[Lease Ends]) FROM Leases AS C WHERE
C.[Property ID] = [A].[Property ID] AND [C].[Lease Ends] < .[Lease
Begins]);

 

[KenSheridan via AccessMonster.com]

I don't think you need the INNER JOIN here as the join criteria are in the
WHERE clause, but I'd normally include the subquery in the SELECT clause for
this sort of thing. Try this:

SELECT L1.[Property ID], L1.[Lease Begins],
L1.[Lease Ends], L1.[Lease Begins] -
(SELECT MAX(L2.[Lease Ends])
FROM Leases As L2
WHERE L2.[Lease Ends] < L1.[Lease Begins]
AND L2.[Property ID] = L1.[Property ID]) AS DaysVacant
FROM Leases AS L1
ORDER BY L1.[Property ID], L1.[Lease Begins];

Ken Sheridan
Stafford, England

Here's the code:

SELECT A.[Property ID], DateDiff("d", [A].[Lease Ends], .[Lease Begins]) AS
DaysVacant
FROM Leases AS A INNER JOIN Leases AS B
WHERE [A].[Lease Ends] = (SELECT Max([C].[Lease Ends]) FROM Leases AS C WHERE
C.[Property ID] = [A].[Property ID] AND [C].[Lease Ends] < .[Lease
Begins]);

Here's some data:
Here's some data:
Number Property Id Lease Begins Lease Ends
1419 1000we 8/10/00 8/10/01
866 1000we 8/17/01 8/10/02
715 1000we 8/11/02 2/28/03
1830 1000we 8/11/02 2/28/03
105 1000we 8/1/08 7/31/09
1423 1014we 8/16/00 8/2/01
110 1014we 8/16/00 8/2/01
2374 1100nw 7/1/04 12/31/04
423 120nh 12/12/96 2/12/02
1641 120nh 12/12/96 2/12/02
933 1212sd 12/15/01 8/10/02
1432 121es 8/13/99 8/31/03
2488 121es 12/21/04 3/1/05
2817 121es 8/20/05 8/15/06
3601 121es 8/21/06 11/27/06
3968 121es 12/6/06 8/20/07
5188 121es 8/15/08 8/10/09
6004 121es 8/11/09 8/10/10
424 121nh 12/12/96 2/12/02
1642 121nh 12/12/96 2/12/02
253 1230tt 7/14/99 6/30/01

Any help would be truly appreciated.
Thank you.

Ummm... could you post the actual SQL you're using? I can't see it from here.

 

[Bibi]

Ken
Thank you very much - it worked like a charm and I was able to move on to
calculate average number of days vacant....
I truly appreciate all the help I have received from the group.
--
TIA
Bibi

I don't think you need the INNER JOIN here as the join criteria are in the
WHERE clause, but I'd normally include the subquery in the SELECT clause for
this sort of thing. Try this:

SELECT L1.[Property ID], L1.[Lease Begins],
L1.[Lease Ends], L1.[Lease Begins] -
(SELECT MAX(L2.[Lease Ends])
FROM Leases As L2
WHERE L2.[Lease Ends] < L1.[Lease Begins]
AND L2.[Property ID] = L1.[Property ID]) AS DaysVacant
FROM Leases AS L1
ORDER BY L1.[Property ID], L1.[Lease Begins];

Ken Sheridan
Stafford, England

Here's the code:

SELECT A.[Property ID], DateDiff("d", [A].[Lease Ends], .[Lease Begins]) AS
DaysVacant
FROM Leases AS A INNER JOIN Leases AS B
WHERE [A].[Lease Ends] = (SELECT Max([C].[Lease Ends]) FROM Leases AS C WHERE
C.[Property ID] = [A].[Property ID] AND [C].[Lease Ends] < .[Lease
Begins]);

Here's some data:
Here's some data:
Number Property Id Lease Begins Lease Ends
1419 1000we 8/10/00 8/10/01
866 1000we 8/17/01 8/10/02
715 1000we 8/11/02 2/28/03
1830 1000we 8/11/02 2/28/03
105 1000we 8/1/08 7/31/09
1423 1014we 8/16/00 8/2/01
110 1014we 8/16/00 8/2/01
2374 1100nw 7/1/04 12/31/04
423 120nh 12/12/96 2/12/02
1641 120nh 12/12/96 2/12/02
933 1212sd 12/15/01 8/10/02
1432 121es 8/13/99 8/31/03
2488 121es 12/21/04 3/1/05
2817 121es 8/20/05 8/15/06
3601 121es 8/21/06 11/27/06
3968 121es 12/6/06 8/20/07
5188 121es 8/15/08 8/10/09
6004 121es 8/11/09 8/10/10
424 121nh 12/12/96 2/12/02
1642 121nh 12/12/96 2/12/02
253 1230tt 7/14/99 6/30/01

Any help would be truly appreciated.
Thank you.

Thank you so much for responding. I had the error message syntax error in
the FROM statement.

Ummm... could you post the actual SQL you're using? I can't see it from here.

 

[Bibi]

HELP! I goofed up. I was not clear enough in stating what I actually needed
from this query. I need to be able to calculate the average number of days
an apartment is vacant BUT the number of days vacant needs to be associated
with the month the original lease ended. Initially the data looked great but
then....I realized that the number of days vacant is being associated with
the new (beginning lease) not the expiring lease..... so the results I get
are not usable to help decide if we should no longer write leases expiring in
a certain month based on how long it takes to find a new tenant.........I
hope this is clearer and you can help!

TIA
Bibi

I don't think you need the INNER JOIN here as the join criteria are in the
WHERE clause, but I'd normally include the subquery in the SELECT clause for
this sort of thing. Try this:

SELECT L1.[Property ID], L1.[Lease Begins],
L1.[Lease Ends], L1.[Lease Begins] -
(SELECT MAX(L2.[Lease Ends])
FROM Leases As L2
WHERE L2.[Lease Ends] < L1.[Lease Begins]
AND L2.[Property ID] = L1.[Property ID]) AS DaysVacant
FROM Leases AS L1
ORDER BY L1.[Property ID], L1.[Lease Begins];

Ken Sheridan
Stafford, England

Here's the code:

SELECT A.[Property ID], DateDiff("d", [A].[Lease Ends], .[Lease Begins]) AS
DaysVacant
FROM Leases AS A INNER JOIN Leases AS B
WHERE [A].[Lease Ends] = (SELECT Max([C].[Lease Ends]) FROM Leases AS C WHERE
C.[Property ID] = [A].[Property ID] AND [C].[Lease Ends] < .[Lease
Begins]);

Here's some data:
Here's some data:
Number Property Id Lease Begins Lease Ends
1419 1000we 8/10/00 8/10/01
866 1000we 8/17/01 8/10/02
715 1000we 8/11/02 2/28/03
1830 1000we 8/11/02 2/28/03
105 1000we 8/1/08 7/31/09
1423 1014we 8/16/00 8/2/01
110 1014we 8/16/00 8/2/01
2374 1100nw 7/1/04 12/31/04
423 120nh 12/12/96 2/12/02
1641 120nh 12/12/96 2/12/02
933 1212sd 12/15/01 8/10/02
1432 121es 8/13/99 8/31/03
2488 121es 12/21/04 3/1/05
2817 121es 8/20/05 8/15/06
3601 121es 8/21/06 11/27/06
3968 121es 12/6/06 8/20/07
5188 121es 8/15/08 8/10/09
6004 121es 8/11/09 8/10/10
424 121nh 12/12/96 2/12/02
1642 121nh 12/12/96 2/12/02
253 1230tt 7/14/99 6/30/01

Any help would be truly appreciated.
Thank you.

Thank you so much for responding. I had the error message syntax error in
the FROM statement.

Ummm... could you post the actual SQL you're using? I can't see it from here.

 

[KenSheridan via AccessMonster.com]

Bibi:

If I understand you correctly you just need to subtract the beginning date of
the next lease from the end date of the current lease:

SELECT L1.[Property ID], L1.[Lease Begins],
L1.[Lease Ends],
(SELECT MIN(L2.[Lease Begins])
FROM Leases As L2
WHERE L2.[Lease Begins] > L1.[Lease Ends]
AND L2.[Property ID] = L1.[Property ID])
- L1.[Lease Ends] AS DaysVacant
FROM Leases AS L1
ORDER BY L1.[Property ID], L1.[Lease Begins];

You can then base another query on this, grouping by MONTH([Lease Ends]) and
returning the AVG(DaysVacant).

Ken Sheridan
Stafford, England

HELP! I goofed up. I was not clear enough in stating what I actually needed
from this query. I need to be able to calculate the average number of days
an apartment is vacant BUT the number of days vacant needs to be associated
with the month the original lease ended. Initially the data looked great but
then....I realized that the number of days vacant is being associated with
the new (beginning lease) not the expiring lease..... so the results I get
are not usable to help decide if we should no longer write leases expiring in
a certain month based on how long it takes to find a new tenant.........I
hope this is clearer and you can help!

TIA
Bibi

I don't think you need the INNER JOIN here as the join criteria are in the
WHERE clause, but I'd normally include the subquery in the SELECT clause for

[quoted text clipped - 53 lines]

 

[Bibi]

Ken
Thank you so much! It's what I needed, and next time I should be able to
figure it out myself.....
--
TIA
Bibi

Bibi:

If I understand you correctly you just need to subtract the beginning date of
the next lease from the end date of the current lease:

SELECT L1.[Property ID], L1.[Lease Begins],
L1.[Lease Ends],
(SELECT MIN(L2.[Lease Begins])
FROM Leases As L2
WHERE L2.[Lease Begins] > L1.[Lease Ends]
AND L2.[Property ID] = L1.[Property ID])
- L1.[Lease Ends] AS DaysVacant
FROM Leases AS L1
ORDER BY L1.[Property ID], L1.[Lease Begins];

You can then base another query on this, grouping by MONTH([Lease Ends]) and
returning the AVG(DaysVacant).

Ken Sheridan
Stafford, England

HELP! I goofed up. I was not clear enough in stating what I actually needed
from this query. I need to be able to calculate the average number of days
an apartment is vacant BUT the number of days vacant needs to be associated
with the month the original lease ended. Initially the data looked great but
then....I realized that the number of days vacant is being associated with
the new (beginning lease) not the expiring lease..... so the results I get
are not usable to help decide if we should no longer write leases expiring in
a certain month based on how long it takes to find a new tenant.........I
hope this is clearer and you can help!

TIA
Bibi

I don't think you need the INNER JOIN here as the join criteria are in the
WHERE clause, but I'd normally include the subquery in the SELECT clause for

[quoted text clipped - 53 lines]

Ummm... could you post the actual SQL you're using? I can't see it from here.

--



.

 

[Bibi]

Ken
Your reply was so helpful. Again, thank you. I thought I could adapt the
code to another very similar situation in a different database , but I
couldn't.
Again I have leases. But this time I would like to run a query that showed
the end date of one lease as First Date Available and the start date of the
next lease (-1 day) as Last Day Available. All the combinations I tired with
code didn't work. Could you help?
--
TIA
Bibi

Bibi:

If I understand you correctly you just need to subtract the beginning date of
the next lease from the end date of the current lease:

SELECT L1.[Property ID], L1.[Lease Begins],
L1.[Lease Ends],
(SELECT MIN(L2.[Lease Begins])
FROM Leases As L2
WHERE L2.[Lease Begins] > L1.[Lease Ends]
AND L2.[Property ID] = L1.[Property ID])
- L1.[Lease Ends] AS DaysVacant
FROM Leases AS L1
ORDER BY L1.[Property ID], L1.[Lease Begins];

You can then base another query on this, grouping by MONTH([Lease Ends]) and
returning the AVG(DaysVacant).

Ken Sheridan
Stafford, England

HELP! I goofed up. I was not clear enough in stating what I actually needed
from this query. I need to be able to calculate the average number of days
an apartment is vacant BUT the number of days vacant needs to be associated
with the month the original lease ended. Initially the data looked great but
then....I realized that the number of days vacant is being associated with
the new (beginning lease) not the expiring lease..... so the results I get
are not usable to help decide if we should no longer write leases expiring in
a certain month based on how long it takes to find a new tenant.........I
hope this is clearer and you can help!

TIA
Bibi

I don't think you need the INNER JOIN here as the join criteria are in the
WHERE clause, but I'd normally include the subquery in the SELECT clause for

[quoted text clipped - 53 lines]

Ummm... could you post the actual SQL you're using? I can't see it from here.

--



.

 

[Bibi]

I just got it to work by deleting the subtraction. Sorry to be such a bother.
TIA
Bibi

Ken
Your reply was so helpful. Again, thank you. I thought I could adapt the
code to another very similar situation in a different database , but I
couldn't.
Again I have leases. But this time I would like to run a query that showed
the end date of one lease as First Date Available and the start date of the
next lease (-1 day) as Last Day Available. All the combinations I tired with
code didn't work. Could you help?
--
TIA
Bibi

Bibi:

If I understand you correctly you just need to subtract the beginning date of
the next lease from the end date of the current lease:

SELECT L1.[Property ID], L1.[Lease Begins],
L1.[Lease Ends],
(SELECT MIN(L2.[Lease Begins])
FROM Leases As L2
WHERE L2.[Lease Begins] > L1.[Lease Ends]
AND L2.[Property ID] = L1.[Property ID])
- L1.[Lease Ends] AS DaysVacant
FROM Leases AS L1
ORDER BY L1.[Property ID], L1.[Lease Begins];

You can then base another query on this, grouping by MONTH([Lease Ends]) and
returning the AVG(DaysVacant).

Ken Sheridan
Stafford, England

HELP! I goofed up. I was not clear enough in stating what I actually needed
from this query. I need to be able to calculate the average number of days
an apartment is vacant BUT the number of days vacant needs to be associated
with the month the original lease ended. Initially the data looked great but
then....I realized that the number of days vacant is being associated with
the new (beginning lease) not the expiring lease..... so the results I get
are not usable to help decide if we should no longer write leases expiring in
a certain month based on how long it takes to find a new tenant.........I
hope this is clearer and you can help!

TIA
Bibi

I don't think you need the INNER JOIN here as the join criteria are in the
WHERE clause, but I'd normally include the subquery in the SELECT clause for
[quoted text clipped - 53 lines]

Ummm... could you post the actual SQL you're using? I can't see it from here.

--



.