forgot the syntax for you;
DATEDIFF(day, startdate, enddate)
Main Topics
Browse All TopicsHi All
I have a list of tenants who live in properties with a start and end date to their contracts. I would like to be able to calculate the amount of days a property was vacant over a date range (void days). Over a period, a property can be fully occupied, part occupied by one tenancy, part/fully occupied 2 or more tenancies or empty. Let me detail a few examples..
Date Range (English Date Format) 01/01/2009 to 31/01/2009
Example 1
Tenant occupies 12/12/2008 and leaves 15/01/2009 with no new tenant occupying the property. The void days for this would be the 16/01/2009 to 31/01/2009=16
Example 2
Tenant1 occupies 12/12/2008 and leaves 03/01/2009 and Tenant2 occupies 16/01/2009 to 15/02/2009. The void days for this would be 04/01/2009 to 15/01/2009=15
Example 3
Tenant1 occupies 12/12/2008 and leaves 03/01/2009, Tenant2 occupies 05/01/2009 and leaves 17/01/2009 and Tenant3 occupies 21/01/2009 and leaves 28/01/2009. The void days would be 04/01/2009=1 plus 18/01/2009 to 20/01/2009=2 plus 29/01/2009 to 31/01/2009=3 which =6 in total
Example 4
Empty Property = 31 void days
Phew.. I hope that is clear. So I can write a procedure in VB to build an array of properties and each day between the date range, go through my tenancies and populate my array values to create a table showing these values, but obviously was wondering if there was any way a query could do such a thing to help me out??
Thanks and I hope I made sense..
Derek.
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.

Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Hi javaboon
I have 2 tables, one listing the properties and the other listing all tenancies linked to the properties. DateDiff is fine(for me) when you have one tenancy showing in your date range but it is more usual to have 2 or more..
The tables show the following relevant detail
Property Table
PropertyID Address
0001 123 Dane Road
0002 345 Glenn Road
0003 543 Baker Street
Tenancy Table
TenantID PropertyID StartDate EndDate
000001 0001 12/12//2009 03/01/2009
000002 0001 05/01/2009 17/01/2009
000003 0001 21/01/2009 28/01/2009
000004 0002 15/12/2008 15/01/2009
So the issues I have really are when there are mulitple tenany records involved within the date range needed..
Kind regards,
Derek.
I have amended the code you gave slightly to suit..
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[sp_voidDays]
@DateFrom datetime,
@DateTo datetime,
@PropertyID uniqueidentifier
AS
BEGIN
SET NOCOUNT ON;
DECLARE @calendar TABLE (date datetime)
DECLARE @currentDate datetime
SET @currentDate = @DateFrom
-- Populate calendar table
WHILE @currentDate <= @DateTo
BEGIN
INSERT INTO @calendar VALUES (@currentDate)
SET @currentDate = DATEADD(day, 1, @currentDate)
END
-- Delete occupied days
DELETE FROM @calendar
FROM @calendar c
INNER JOIN [Tenancy] t ON t.PropertyFK = @PropertyID
WHERE c.date BETWEEN t.dteOccupiedDate AND t.dteVacatingDate
SELECT @PropertyID PropertyFK, COUNT(*) VoidDays FROM @calendar
END
Business Accounts
Answer for Membership
by: javaboonPosted on 2009-10-28 at 09:35:04ID: 25685145
Hi Derek, a table structure would be helpfull in this matter but generally speaking one can use datediff function to calculate the difference between dates.