Query for gaps in date ranges

[kagoodwin13]

I am compiling a database of logistics contracts. I am in the stage now of finding gaps in data, so that I can find this data and build a more comprehensive data set.

I would like to build a query that can find a gap in a range of dates. For instance, there may be two prior contracts to run a truck from Oshkosh, WI to Hackensack, NJ. The first contract may have an effective date of 1/1/2010 and expire 12/31/2010, but the second contract may have an effective date of 1/1/2012 and expire 12/31/2012. This means there is a gap for 2011.

Is there a way to query/report these gaps back? My database knowledge is pretty limited, so I'm wondering if this is even possible.

Thanks in advance for any help.

[kagoodwin13]

I found this string of code on an Oracle forum here: https://forums.oracle.com/forums/thr...readID=2182689

I'm not sure Access has this capability, but perhaps it might help with finding a solution:

with sample_data as (
  select 1 employee_id, 'Active' employment_status, date '1990-01-01' beg_date, date '1991-01-01' end_date from dual union all  select 1, 'Leave', date '1991-02-01', date '1993-06-03' from dual union all  select 1, 'Active', date '1993-06-04', date '1995-02-01' from dual union all  select 1, 'Fired', date '2000-06-01', date '2299-12-31' from dual)select employee_id,       employment_status as last_status,       end_date as gap_lower_bound,       next_date as gap_upper_bound,       next_statusfrom (  select t.*,         lead(beg_date) over(partition by employee_id order by beg_date) next_date,         lead(employment_status) over(partition by employee_id order by beg_date) next_status  from sample_data t)where next_date > end_date + 1;

[orange]

I am compiling a database of logistics contracts

What tables do you have? What is the data source?
Does your finance area have the info electronically? If so, what format?

Access does not have the same Partition Over construct as Oracle. Are you working in Access or Oracle?

Someone may have written User function(s) in vba(Access) to produce similar result as the Oracle function.

[kagoodwin13]

I have a flat table with the following fields: Origin, Destination, Carrier, Effective Date of Contract, End Date of Contract, Rate for Size 1, Rate for Size 2, Rate for Size 3.

I am working with contractual rates, and the database is for a purchasing/contract negotiation group. Finance would have actual or invoiced data, but my group/client is more concerned with contracted rates.

The reason they look at contracted rates is to see performance in negotiation. Actual/invoiced rates change based on any given day's fuel prices, for example, but contracted rates stay static over the contract. My clients want to be able to see how their carriers have changed contracted rates over time to determine where/when rates are being increased.

I am working in Access. I thought there may be an easy way to translate Oracle SQL/VBA to Access SQL/VBA given the relative similarity of the two languages.

[orange]

Are your contracts always for Origin_Destination pairs? Same start/End dates?
What's the significance of Size1,2 ,3?

Just curious.
There is a Partition function in Access http://office.microsoft.com/en-us/ac...001228892.aspx
but it is different than Oracle

[kagoodwin13]

Yes, contracts are always for origin/destination pairs.

Size 1, 2, and 3 have different rates because the contracted cost is by unit moved. Basically, a full truck always costs the same amount, but it takes more size 1 units than size 2 units to fill the truck. Sometimes there isn't always a full truck though, hence the per-unit cost. I can't really get into any more detail due to confidentiality.

[orange]

I have made a mock up of a few contracts. There is a module with a procedure (ReadContracts) to read the records and identify contracts that are "missing a contract"--- that is the end dat of one contract and the start date of the next are not sequential.
The procedure is a mock up of logic to determine the sequence and if a "missing contract" is found, a message is written to the immediate window.


Sample output from my test data:

Boston-Miami with Startdate 01/01/2011 OK
Boston-Nashville with Startdate 01/01/2008 OK
New York-Chicago Dates NOT in sequence -- contract with Startdate 01/01/2006 previous EndDate 2004 id-> 4
New York-Chicago Dates NOT in sequence -- contract with Startdate 01/01/2009 previous EndDate 2007 id-> 1
New York-Chicago with Startdate 01/01/2011 OK
New York-Phoenix with Startdate 01/01/2009 OK

I hope it's useful to you.

It is an Acc2003 mdb meant for ideas and concepts.