need to find date range

Ateeq

Oracle 11.2.0.4

Forms 6i

A user enters the date range From and To and the query needs to show which properties are vacant during this date range.

I have two tables SERVICE_UNITS and OCCUPANCY.

Service units table has property_code and Property_address while Occupancy table has property_code, status (Active,New), start_date and End_date

Suppose a property is active from 16-NOV-2022 till 20-NOV-2022 and an advance booking has been done for same property from 25-NOV-2022 till 30-NOV-2022.

how can i show the properties which aren't booked during any given range.

Best Regards,

BEDE

I think it should be something like below:

select *
from units u
where not exists (
  select 1
  from occupancy oc
  where u.property_code=oc.property_code
    and oc.status='OCCUPIED'
    and (
      oc.start_date between :from_date and :to_date
      or oc.end_date between :from_date and :to_date
      or :from_date between oc.start_date and oc.end_date
      or :end_date between oc.start_date and oc.end_date
    )
)  

Still, I'm not sure I got the right column names and the right code for the status, for you haven't actually given the DDL for those tables.

Also, I'm not sure what other info you may need to fetch using joins.

If it's a Forms application, then that data block should be based on a view which fetches all the data in one go.

BEDE

I think it should be something like below:

select *
from units u
where not exists (
  select 1
  from occupancy oc
  where u.property_code=oc.property_code
    and oc.status='OCCUPIED'
    and (
      oc.start_date between :from_date and :to_date
      or oc.end_date between :from_date and :to_date
      or :from_date between oc.start_date and oc.end_date
      or :end_date between oc.start_date and oc.end_date
    )
)  

Still, I'm not sure I got the right column names and the right code for the status, for you haven't actually given the DDL for those tables.

Also, I'm not sure what other info you may need to fetch using joins.

If it's a Forms application, then that data block should be based on a view which fetches all the data in one go.

mathguy

I guess the "status" in the OCCUPANCY table is irrelevant - right? A property is "occupied" for every range (from the START_DATE to the END_DATE) that appears in the table, regardless of STATUS.

If this assumption is incorrect, please explain how different STATUS values mean different things for the problem you asked. What is "new" and what is "active", and what is an "advance booking" (you didn't explain how that is reflected in the tables)? Or am I correct, and for the question you need to answer, you don't need to look at STATUS?

Assuming that the user inputs are given as bind variables, as BEDE showed - with names FROM_DATE and TO_DATE - the way to find the properties that aren't booked on any of the days in the range between FROM_DATE and TO_DATE is something like this:

select *
from   service_units u
where  property_code not in
         (
           select property_code
           from   occupancy
           where  start_date <= :to_date
             and  end_date   >= :from_date
         )
;

The query is easy to understand if you understand the following about intervals (what you call "ranges"). Say you have two ranges, one is from A to B and the other is from C to D (where A < B and C < D). You want to know when they overlap. Well, they don't overlap if either B < C (in that case the whole interval from A to B falls before C), or if D < A (then the entire interval from A to B is entirely after D). Reversing this, the intervals do overlap if and only if both of those conditions are false; that is, if C <= B and A <= D. That is exactly the condition in the subquery in the IN condition.

The main query searches for properties where the input range does not overlap with any of the ranges - for the same property - in the OCCUPANCY table.

BluShadow

And to explain that visually...

There are 5 cases for overlapping ranges (not necessarily intervals as this can be applied to date, or numeric ranges, or in theory anything you can compare with >, <, >= or <= etc.)

(1) range 1 is entirely before range 2

(2) range 1 overlaps the start of range 2

(3) range 1 is entirely within (or same as) range 2

(4) range 1 overlaps the end of range2

(5) range 1 is entirely after range 2

In your case you're looking for a required range and comparing it to (an)other range(s) and you want to ensure it doesn't overlap.

(1)
Required: S-----E
Compare :         S-------E
Result  : OK
(2)
Required:    S-----E
Compare :         S-------E
Result  : NOT OK
(3)
Required:          S-----E
Compare :         S-------E
Result  : NOT OK
(4)
Required:               S-----E
Compare :         S-------E
Result  : NOT OK
(5)
Required:                   S-----E
Compare :         S-------E
Result  : OK

To check if overlapping (not ok)...

a)  Required "S"tart <= Compare "E"nd = Cases 1,2,3,4

b)  Required "E"nd >= Compare "S"tart = Cases 2,3,4,5

For both to be true, that must be Cases 2,3 and 4

So, we know there is an overlap in cases 2,3,4 and therefore not in 1 and 5

Logic for overlapping is simply

where range1.start <= range2.end
and  range1.end >= range2.start

If you want where they are not overlapping then logically

where NOT (   range1.start <= range2.end
      and  range1.end >= range2.start
     )

Or, logical equivalent

where (  range1.start > range2.end
    or range1.end < range2.start
   )

How you apply that in your code/SQL is up to you.... previous responses have already given examples.

Frank Kulash

Hi, @Ateeq

Did the any of replies above answer the question? If so, mark the thread as "Answered" by clicking on the "Yes" button at the end of the helpful comment(s):

If none of the replies answered the question, then post a little sample data (CREATE TABLE and INSERT statements for all tables needed) and the exact results you want from that sample data. Explain, in general terms, how you get the desired results from the data. You should include these things whenever you have a question.

Ateeq

Thank You all for the ideas,