Access query to return records not found between date range

1
votes

I have the following sample data 

Table1      
REF_NO  SUPP_START_DATE SUPP_END_DATE
123     01/01/2018      31/12/2018
456     01/01/2017      31/12/2017
789     01/01/2016      31/12/2016

Table2      
REF_NO  CHG_START_DATE  CHG_END_DATE
123     01/03/2018      31/03/2018
123     01/04/2018      30/04/2018
456     01/02/2018      28/02/2018
456     01/01/2017      31/01/2017
789     01/07/2016      31/07/2016

I'd like to know if it is possible to in Access SQL to return all charges (table2) that do not fall between the start and end dates of table1. So, with the sample data above, the following would be returned :-

Results     
REF_NO  CHG_START_DATE  CHG_END_DATE
456     01/02/2018      28/02/2018

I know how to join the 2 tables by using 

SELECT table1.ref_no, table2.CHG_START_DATE, table2.CHG_END_DATE
FROM table1 
LEFT JOIN table2 ON table1.ref_no = table2.ref_no

but I'm not sure how to cater for the date mismatches

2
sqlms-access

2 Answers

2
votes

If it is sufficient to just look at the start and end dates:

select t2.*
from table2 t2
where not exists (select 1
                  from table1 as t1
                  where t1.refno = t2.refno and
                        t2.CHG_START_DATE between UPP_START_DATE and SUPP_END_DATE
                 ) or
      not exists (select 1
                  from table1 as t1
                  where t1.refno = t2.refno and
                        t2.CHG_END_DATE between UPP_START_DATE and SUPP_END_DATE
                 ) ;
0
votes

Here's an alternative approach using joins instead of correlated subqueries:

select t2.* from table1 t1 inner join table2 t2 on t1.ref_no = t2.ref_no 
where not
(
    (t2.chg_start_date between t1.supp_start_date and t1.supp_end_date) and
    (t2.chg_end_date   between t1.supp_start_date and t1.supp_end_date)
)