Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2002
    Posts
    227

    Unanswered: problem with date......

    Hi,
    I've these tables:

    ROOMS:

    BUILD......FLOOR.........ROOM
    1...........4.............426
    2...........1.............107
    1...........3.............313
    4...........1.............91
    2...........3.............304
    12..........7.............274
    17..........1.............112
    17..........1.............304
    18..........2.............184
    24..........2.............61
    16..........5.............102
    19..........1.............121
    2...........2.............213

    occup_rooms: (build - floor - room foreign key on build,floor,room of table rooms)

    This is the table of the occupied rooms:

    BUILD......FLOOR.........ROOM........DATE_START... ......DATE_END
    1...........4.............426........01/10/2003.........01/01/2004
    2...........1.............107........01/11/2003.........28/11/2003
    1...........3.............313........02/09/2003.........10/09/2003
    4...........1.............91.........02/12/2003.........01/01/2004
    2...........3.............304........23/10/2003.........24/12/2003
    12..........7.............274........23/10/2003.........24/12/2003
    17..........1.............112........23/10/2003.........24/12/2003
    17..........1.............304........02/12/2003.........02/01/2004
    18..........2.............184........02/12/2003.........02/01/2004
    24..........2.............61.........01/09/2003.........29/11/2003
    16..........5.............102........03/01/2004.........29/03/2004
    19..........1.............121........03/02/2004.........29/03/2004
    2...........2.............213........03/10/2004.........29/10/2004

    I'd like to find all the free rooms ( in the period comprised between the DATE_START and DATE_END).


    For example if I get in input these date:
    DATE_START = 01/12/2003
    DATE_END = 02/01/2004

    output is:

    BUILD......FLOOR.........ROOM
    2...........1.............107
    1...........3.............313
    24..........2.............61
    16..........5.............102
    19..........1.............121
    2...........2.............213

    build=2 - floor=1 - room=107 occupied in 1/11/2003 - 28/11/2003
    free in 01/12/2003 - 02/01/2004

    I tried with 2 step:

    CREATE OR REPLACE VIEW OCCUP (BUILD,
    FLOOR, ROOM, DATE_START, DATE_END ) AS select a.BUILD,a.FLOOR,a.ROOM,b.date_start,b.date_end
    from rooms a, occup_rooms b
    where a.BUILD=b.BUILD
    and a.FLOOR=b.FLOOR
    and a.ROOM=b.ROOM
    and (b.date_start >= TO_DATE('01-12-2003', 'dd-mm-yyyy')
    AND b.date_end <= TO_DATE('02-01-2004', 'dd-mm-YYYY'))


    select build,floor,room
    from rooms
    where (build,floor,room) not in (select build,floor,room
    from occup)


    But I'd like create just one query, because I must create a stored procedure that have in input DATE_START and DATE_END parameters.

    How can I create a query oracle to find free rooms with DATE_START and DATE_END?


    Thanks in advance!!!

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Code:
    select build,floor,room
    from rooms
    where (build,floor,room) not in 
    ( select a.BUILD,a.FLOOR,a.ROOM
      from occup_rooms b
      where (b.date_start >= TO_DATE('01-12-2003', 'dd-mm-yyyy')
      and b.date_end <= TO_DATE('02-01-2004', 'dd-mm-YYYY'))
    )

  3. #3
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Tony, your solution only covers one case, while there are more (if I understand the OP's problem well) : only one occupied day in the given period should mark the room as occupied.

    Say you want unoccupied rooms between D1 and D2, the occupied rooms are the ones like : (SD is start_date and ED is end_date)

    Case 1.1 : (SD < D1) and (ED between D1 and D2)
    Code:
                         D1----------------------D2
       SD--------------------ED
    Case 1.2 (the one you cover, there are subcases with equalities between D1 and SD or D2 and ED, but it's not important here) : (SD between D1 and D2) and (ED between D1 and D2) (and (SD < ED) to be perfect)
    Code:
                         D1----------------------D2
                              SD-------------ED
    Case 1.3 : (SD between D1 and D2) and (ED > D2)
    Code:
                         D1----------------------D2
                              SD-------------------------------ED
    So to sum up case 1 :

    (SD between D1 and D2) or (ED between D1 and D2)

    Case 2 : (SD < D1) and (ED > D2)
    Code:
                         D1----------------------D2
       SD------------------------------------------------ED
    So, I think a correct solution would be :

    Code:
    select build,floor,room
    from rooms
    where (build,floor,room) not in 
    ( 
       select a.BUILD,a.FLOOR,a.ROOM
       from occup_rooms b
       where 
       (
          (
             (b.date_start between TO_DATE('01-12-2003', 'dd-mm-yyyy') and TO_DATE('02-01-2004', 'dd-mm-YYYY'))
             or
             (b.date_end between TO_DATE('01-12-2003', 'dd-mm-yyyy') and TO_DATE('02-01-2004', 'dd-mm-YYYY'))
          )
          or
          (
             b.date_start < TO_DATE('01-12-2003', 'dd-mm-yyyy') 
             and b.date_end > TO_DATE('02-01-2004', 'dd-mm-YYYY')
          )
       )
    )
    I think that does it.

    HTH and Regards,

    RBARAER

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You are probably correct. All I did was reformat Raf's code so that it did whatever it did in one query rather than creating a table as an intermediate step!

  5. #5
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    You effectively answered what raf was asking, so maybe it was really what he wanted.

    I think either I did something too complicated or I just helped you greatly raf, what do you think ?

    Regards,

    RBARAER

  6. #6
    Join Date
    Jul 2002
    Posts
    227
    RBARAER,
    you understand the problem well!!

    I tried this:


    select build,floor,room
    from rooms
    where (build,floor,room) not in
    (select a.BUILD,a.FLOOR,a.ROOM
    from rooms a, occup_rooms b
    where a.BUILD=b.BUILD
    and a.FLOOR=b.FLOOR
    and a.ROOM=b.ROOM
    AND((b.date_start <= TO_DATE(my_date_start , 'dd-mm-YYYY')
    AND b.date_end >= TO_DATE(my_date_end, 'dd-mm-YYYY'))
    OR ( b.date_end <= TO_DATE(my_date_end, 'dd-mm-YYYY'))
    AND b.date_end >= TO_DATE(my_date_start , 'dd-mm-YYYY')
    OR ( b.date_start >= TO_DATE(my_date_start , 'dd-mm-YYYY')
    and b.date_start <= TO_DATE(my_date_end, 'dd-mm-YYYY')
    AND b.date_end >= TO_DATE(my_date_end, 'dd-mm-YYYY'))))

    seem correct.......Have you any idea?

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    To look for rows that do not overlap the specified period (my_date_start, my_date_end) all you need to do is:
    Code:
    select b.BUILD,b.FLOOR,b.ROOM
    from occup_rooms b
    where b.date_end < TO_DATE(my_date_start , 'dd-mm-YYYY') 
    or b.date_start > TO_DATE(my_date_end , 'dd-mm-YYYY');
    To look for rows that do overlap the specified period (my_date_start, my_date_end) all you need to do is:
    Code:
    select b.BUILD,b.FLOOR,b.ROOM
    from occup_rooms b
    where b.date_end >= TO_DATE(my_date_start , 'dd-mm-YYYY') 
    and b.date_start <= TO_DATE(my_date_end , 'dd-mm-YYYY');

  8. #8
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    I think you're right Tony, and it's much simpler.

    BTW raf, your solution looks very complicated, and you don't need the join you perform. (I believe we confused you by keeping a and b where only b was necessary, an error Tony corrected in his last reply).

    So a solution would be :
    Code:
    select build,floor,room
    from rooms
    where (build,floor,room) not in 
    (
    select b.BUILD,b.FLOOR,b.ROOM
    from occup_rooms b
    where b.date_end >= TO_DATE(my_date_start , 'dd-mm-YYYY') 
    and b.date_start <= TO_DATE(my_date_end , 'dd-mm-YYYY')
    and b.date_start < b.date_end 
    )
    The last condition in Italic is not mandatory and should not be necessary if data is consistent (ie an end date should always be greater than its corresponding begin date).

    Regards,

    RBARAER

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •