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
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
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
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
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..