The way I would approach this, is to do a running sum of all the statuses. Anywhere the sum is larger than 1, there is an overlap happening.
This note I wrote may help solve the problem.
Can you elaborate on this. I think this might be on the right path but not quite, although I might be missing something. When I do the running total it gives me numbers larger than one when the status is used multiple times. For example if Vacant occurs twice on different dates I get the number 2 but that's not what I'm looking for. That scenario is O.K., it's possible for the units to have the same status on different dates.
What I need to find is if different statuses occur at the same time. For example Vacant unrented from 01.01.2015 - 01.20.2015 and Vacant rented from 01.01.2015-01.15.2015. Although the statuses are different the dates are overlapping.
What about those without end date?
On Thu, Nov 5, 2015 at 1:09 PM, Tracey Meacock <
The statuses without end dates are the unit's current status. I would need to check to see that their start dates don't overlap but it's not a problem that they don't have end dates.
Here you go the solution. See if it works for you.
It is based on the article with link above. Note that the end date is
shifted by one day because we need the first date the status is no more
effective. It is the day after the end date.
On Thu, Nov 5, 2015 at 1:43 PM, Tracey Meacock <
1 of 1 people found this helpful
The attached uses a self cross join and combines method 6 and method 1 in The Cross Join Collection.
This is the cross join used in attached workbook using SQL Jet syntax with Excel Legacy Connector:
SELECT d1.[Unit] , d1.[Unit Status] , d1.[Unit Status Start] , d1.[Unit Status End] , d2.[Unit] as [Unit 2] , d2.[Unit Status] as [Unit Status 2] , d2.[Unit Status Start] as [Unit Status Start 2] , d2.[Unit Status End] as [Unit Status End 2] FROM [Sample Data$] d1, [Sample Data$] d2 WHERE d1.[Unit] = d2.[Unit] and d1.[Unit Status Start] <= iif(d2.[Unit Status End] is null, #1/1/2099#, d2.[Unit Status End]) and iif(d1.[Unit Status End] is null, #1/1/2099#, d1.[Unit Status End]) >= d2.[Unit Status Start] and not ( d1.[Unit Status Start] = d2.[Unit Status Start] and iif(d1.[Unit Status End] is null, #1/1/2099#, d1.[Unit Status End]) = iif(d2.[Unit Status End] is null, #1/1/2099#, d2.[Unit Status End]) )
Line 11 to 12 is method 1 in The Cross Join Collection.
Line 13 to 17 is about excluding comparison of the same row. It would be better if your data had an ID for each row. In its place, I just compared all fields and assumed that if all are the same, it is the same original data row.
The ugly iif code is about having a dummy future value instead of null and thus include null in the comparison.
In case Excel (or a Text file) is your data source, you will need to open the file with the Legacy connector:
Below is a screenshot from attached workbook:
Resources (re-use solutions)
A link is made to this comment under method 6 in The Cross Join Collection.
A reference is also made to your question in FAQ: Open & Close Dates.
Attached Workbook Version: 9.0