MS Access A Different Between Dates Problem

[chi2jjk]

First, Thank you.
I have a table (A) with close to 1000 distinct dates. I have another table (B) with hundreds of thousands of records, each of which contains a "From Date" and "To Date". I need to find all of the records (Dates) in table A that are not encapsulated between any of the From-To dates in table B. In layman's terms, which dates in table A were missed in table B. Table B may have From-To overlaps, or gaps. There is no relation between tables A & B other than checking if date in table A is between From-To in any record in table B.

Again, thank you for your help

[patjones]

Try this SQL:

SELECT tblA.date
FROM tblA
WHERE NOT EXISTS (SELECT tblA.date
                  FROM tblA
                  WHERE tblA.date BETWEEN tblB.from_date AND tblB.to_date);

Pat

[chi2jjk]

Pat, thank you. It seems not to work prompting me for the 'tblB.from_date '. Does tblB need to be referenced in a FROM clause somehwere?
Original code which seems to have produced a cartesian table with almost 2 million records is here:

SELECT tblA.CheckDate
FROM tblA 
LEFT JOIN tblB 
ON ( tblA.CheckDate <= tblB.ToDate) AND (tblA.CheckDate >= tblB.FromDate);

[patjones]

Actually there is a small issue with the SQL I gave you. I made a correction and am just testing it out now in my sandbox database.

Pat

[patjones]

OK, try this instead...

SELECT tblA.date
FROM tblA
WHERE tblA.date NOT IN (SELECT tblA.date
                        FROM tblA 
                        WHERE EXISTS (SELECT *
                                      FROM tblB
                                      WHERE tblA.date BETWEEN tblB.from_date AND tblB.to_date));

The two innermost queries check each date in tblA to see if there is a date range match in tblB. As soon as there is a match, the EXISTS condition is met and no further records in tblB are checked. The result of this process is all the dates in tblA that do match a range in tblB. The outermost part of the query simply picks out records that are not in that list, which is the aim of your query.

Pat

[Rabbit]

I don't know if that will work. Try this.

SELECT tblA.date 
FROM tblA
WHERE NOT EXISTS (SELECT *
                  FROM tblB
                  WHERE tblA.date BETWEEN tblB.from_date AND tblB.to_date);

[patjones]

It works in my sandbox database...

I also tested the NOT EXISTS method, which works nicely - and considered posting that; but if you use NOT EXISTS then every record in tblB has to be checked, right? If you use EXISTS then the test succeeds on the first record in tblB where the record in tblA has a match and the next record in tblA can be checked?

I'm thinking from a performance standpoint here, but it probably doesn't matter for a small number of records.

[chi2jjk]

Thank you both. Unfortunately, I will have to have a go with these ideas in the morning as my shift is over for today. Cheers! And thank you emmensly for all of your help today!

[patjones]

It's no problem. I do believe that you have two viable options. Let us know if it works.

[Rabbit]

Sorry, you're right, yours should work. I was just worried about the ambiguous qualifications and whether it would resolve them correctly or throw an error.

[NeoPa]

Here's another one. Try :

SELECT A.DateField
FROM   [A]
       LEFT JOIN
       [B]
  ON   A.DateField Between B.FromDate And B.ToDate
WHERE  B.FromDate Is Null

[chi2jjk]

Pat, your query worked a treat! I did not need to try additional offers - although I am very grateful to each of you, and I have learned from this experience.

[patjones]

Thanks so much for letting us know, chi2jjk.

Because I was interested in knowing, I decided to look further into the performance of the three methods presented. So in SQL Server I created a table with 1000 dates, and a table with 1000 date ranges, then used each of the three methods from our thread.

In terms of total execution time, NeoPa's JOIN method did best at 203 ms, followed by ADezii's NOT EXISTS query at 234 ms, and my EXISTS query at 249 ms. Of course, these differences are small potatoes, but I wonder what these figures would look like at say one million records.

I really thought I had a good performing query, and it faired worst! :-/

[NeoPa]

That's interesting info Pat. I suspect you're referring to Rabbit's NOT EXISTS SQL rather than ADezii's, but that's just a detail. I must admit I was happy to find something that didn't require a subquery (although I admit I struggled there for a bit).

[Rabbit]

Well it's hard to measure the time it takes for a query to run. Each query may cache additional info that may cause subsequent queries to run quicker. And each query that's run isn't sterile since there may be other processes running that's taking up CPU time and memory.