SQL - Date range falls between given date rang

Question

I am trying to find all the lease that does not fall in the rental period. The structure of my tables are as follows:

Unit Availability table:

Unit Key  Start Date    End Date  Availability
     1     1/1/2015     6/1/2015       1
     1     6/2/2015   12/31/2015       0
     1     1/1/2016   12/31/2016       1
     2     1/1/2015     6/1/2015       1
     2     6/2/2015   12/31/2015       0

Lease table:

Unit Key  Lease Start  Lease End
     1     1/1/2015     6/1/2015
     1     7/2/2015     8/2/2015

I am looking to write a logic to determine all the lease that does not fall in the availability period. In this case, the 2nd row in the lease table should be the output.

Any kind of help or lead will be much appreciated.

It's considered very rude here to post an image instead of sample data instead of text for the data. You're new, so you get a pass, but text makes it easier for us to set up a sample table to make sure our answers are good. — Joel Coehoorn, Jul 16 '17 at 04:47
Thank you, Joel, I wasn't sure how to do that. But thank you for edit. I really appreciate your help. — Ashwin, Jul 16 '17 at 18:12

score 0 · Answer 1 · answered Jul 16 '17 at 06:11

0

You should use NOT EXISTS statement :

SELECT * FROM LEASE L
WHERE NOT EXISTS (SELECT * FROM AVAILABILITY A
  WHERE A.UNIT_KEY = L.UNIT_KEY
  AND A.AVAILABILITY = 1
  AND A.START_DATE <= L.LEASE_START
  AND A.END_DATE >= L.LEASE_END);

answered Jul 16 '17 at 06:11

p1erstef

384
4
11

score 0 · Answer 2 · answered Jul 16 '17 at 12:26

I'm guessing that Availability = 0 means that it isn't available.

select distinct
  Lease.*
from
  Lease
  join UnitAvailability on
    Lease.UnitKey = UnitAvailability.UnitKey
    and Lease.LeaseStart <= UnitAvailability.EndDate
    and Lease.LeaseEnd >= UnitAvailability.StartDate
where
  Availability = 0

SQL - Date range falls between given date rang

2 Answers2