troubleshooting Question

Calculating Void Days

Avatar of Delboy
DelboyFlag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft SQL ServerSQL
18 Comments1 Solution502 ViewsLast Modified:
Hi 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.


ASKER CERTIFIED SOLUTION
pcelba

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 18 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 18 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros