Unanswered: Missing date in a period
I have a table (rental), that contains
Entry id (unique inremental id)
Office No (a number related to another table on office adresses)
Car No (a number related to another on cars and their specs)
Rented From (dd-mm-yyyy)
Rented To (dd-mm-yyyy)
What I am looking for is a way to isolate/detect cars with unaccounted for dates.
E.g. I need to check if any car has days or periods where they aren't accounted for within a specific year.
So far my approaches query-wise haven't been succesful.
a) A very basic approach. I built a table that contained all dates in a year, and then queried every entry in rental table for cases where the date didn't go between from and two dates. While certainly a solution, its quite heavy to run.
b) the approach I'm working on right now, is to join the rented to date with the rented from date for the same car and office, hoping to build a "chain" a-b, b-c, c-d and catching to dates with no equivalent from date.
I just cant seem to work it out right.
IT squid: networks, servers, firewalls, routers and I dabble a little with SQL-server and Access as well....