Results 1 to 13 of 13
  1. #1
    Join Date
    Nov 2005
    Posts
    13

    SQLPLUS Query using date

    In Oracle using SQLPLUSI am trying to produce a query to

    ----List all library books rented for the three weeks following the first Wednesday in October . The date must be derived using SQL

    Any help greatly appreciated
    Many Thanks,
    BB.

  2. #2
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    what's your table structure? and you could post some example as well...

  3. #3
    Join Date
    Nov 2005
    Posts
    13

    re:

    I want to display:

    book_title field from the book table
    query the borrow_date field from the borrower table

  4. #4
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    ok, you didn't specify so I assume:
    relationship between BORROWER and BOOK is: many to one
    BORROWER has:
    BOOK_ID (foreign key to BOOK)
    START_DATE (when rent started)
    END_DATE (when rent ended)
    BOOK has:
    ID (primary key)
    Code:
    select book.book_title
    from borrower, book
    where borrower.book_id = book.id
    and trunc(borrower.start_date) = trunc(next_day(to_date('01-OCT-05'), 'WED'))
    and trunc(borrower.end_date) = trunc(next_day(to_date('01-OCT-05'), 'WED') + 21)
    Last edited by madafaka; 11-23-05 at 16:26.

  5. #5
    Join Date
    Nov 2005
    Posts
    13
    How would I list all the books released for the 2 weeks following the first Wednesday in April based on the release_date? I need to use sysdate not code the dates in.

    release_date in Book table

    isbn_no in Book table (foreign key)
    isbn_no in ISBN table (primary key)

  6. #6
    Join Date
    Nov 2005
    Posts
    17
    Sounds like another academic assignment? Why use SYSDATE if you want between the first Wednesday in April and two weeks following? You have specified the month as April not as today's date.

  7. #7
    Join Date
    Nov 2005
    Posts
    13
    sorry I've gone mad and I dont even know what I'm talking about and yes you're right.. it's not 'real life' stuff.

  8. #8
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,435
    An example of using sysdate. What was borrowed in the first 21 days from the first wednesday for the previous month.


    select book.book_title
    from borrower, book
    where borrower.book_id = book.id
    and trunc(borrower.start_date) between next_day(trunc(add_months(sysdate,-1),'MON')-1,'WED') and next_day(trunc(add_months(sysdate,-1),'MON')-1,'WED')+21
    or trunc(borrower.end_date) between next_day(trunc(add_months(sysdate,-1),'MON')-1,'WED') and next_day(trunc(add_months(sysdate,-1),'MON')-1,'WED')+21;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  9. #9
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    Quote Originally Posted by beilstwh
    An example of using sysdate. What was borrowed in the first 21 days from the first wednesday for the previous month.


    select book.book_title
    from borrower, book
    where borrower.book_id = book.id
    and trunc(borrower.start_date) between next_day(trunc(add_months(sysdate,-1),'MON')-1,'WED') and next_day(trunc(add_months(sysdate,-1),'MON')-1,'WED')+21
    or trunc(borrower.end_date) between next_day(trunc(add_months(sysdate,-1),'MON')-1,'WED') and next_day(trunc(add_months(sysdate,-1),'MON')-1,'WED')+21;
    OK, but what if you'll run this statement in december or next january?

  10. #10
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    Quote Originally Posted by baby_boomerang
    How would I list all the books released for the 2 weeks following the first Wednesday in April based on the release_date? I need to use sysdate not code the dates in.

    release_date in Book table

    isbn_no in Book table (foreign key)
    isbn_no in ISBN table (primary key)
    I can't see the point of using ISBN table, you have Book Name and Releas Date in Book table.

    The only reason why to use sysdate could be the first Wednesday in April is different date (DD/MM) every year.
    e.g.
    02-APR-2003
    07-APR-2004
    06-APR-2005

    so you use sysdate just to retreive year:
    Code:
    select 
    book.book_title
    from book
    where trunc(release_date) 
    between trunc(next_day(to_date('31-MAR-' || to_char(sysdate, 'YYYY'), 'DD-MON-YYYY'), 'WED'))
    and	trunc(next_day(to_date('31-MAR-' || to_char(sysdate, 'YYYY'), 'DD-MON-YYYY'), 'WED') + 14)
    Last edited by madafaka; 11-23-05 at 17:08.

  11. #11
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,435
    Quote Originally Posted by madafaka
    OK, but what if you'll run this statement in december or next january?
    If you ran the query in december, it would show the first 21 days from the first wednesday in november. If ran in january, then the info for december....etc
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  12. #12
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    Quote Originally Posted by beilstwh
    If you ran the query in december, it would show the first 21 days from the first wednesday in november. If ran in january, then the info for december....etc
    Exactly, and the requirement was:
    Quote Originally Posted by baby_boomerang
    ----List all library books rented for the three weeks following the first Wednesday in October .

  13. #13
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,435
    True, What I posted was a general query as a lession for the user, the answer for the october query was given earlier.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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