Forum Stats

  • 3,875,427 Users
  • 2,266,915 Discussions
  • 7,912,205 Comments

Discussions

need to find date range

Ateeq
Ateeq Member Posts: 22 Green Ribbon
edited Nov 17, 2022 5:28AM in SQL & PL/SQL

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,

Tagged:

Best Answer

  • BEDE
    BEDE Oracle Developer Member Posts: 2,475 Gold Trophy
    edited Nov 17, 2022 6:55AM Answer ✓

    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.

Answers

  • BEDE
    BEDE Oracle Developer Member Posts: 2,475 Gold Trophy
    edited Nov 17, 2022 6:55AM Answer ✓

    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
    mathguy Member Posts: 10,912 Black Diamond

    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.

    Mohamed Houri
  • BluShadow
    BluShadow Member, Moderator Posts: 42,580 Red Diamond

    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.

    Mohamed Houri
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,780 Red Diamond
    edited Nov 17, 2022 6:09PM

    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
    Ateeq Member Posts: 22 Green Ribbon

    Thank You all for the ideas,