If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Oracle > SQLPLUS Query using date

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Feb 2004
Location: Dublin, Ireland
Posts: 212
what's your table structure? and you could post some example as well...
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
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 15:26.
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
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)
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #8 (permalink)  
Old
Lead Application Develope
 
Join Date: Jun 2004
Location: Liverpool, NY USA
Posts: 2,415
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.
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
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?
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
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 16:08.
Reply With Quote
  #11 (permalink)  
Old
Lead Application Develope
 
Join Date: Jun 2004
Location: Liverpool, NY USA
Posts: 2,415
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.
Reply With Quote
  #12 (permalink)  
Old
Registered User
 
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 .
Reply With Quote
  #13 (permalink)  
Old
Lead Application Develope
 
Join Date: Jun 2004
Location: Liverpool, NY USA
Posts: 2,415
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.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On