Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2004
    Posts
    41

    Unanswered: Same old Dates thing again

    Sorry i know my major weakness is dates, but one last problem! I had this solved but its playing up again!I have to do a query which allows the user to input the outlet he/she wants to rent a car from and the cars that are available should be displayed! My code is:

    SELECT DISTINCT Vehicle_Detail.make, Vehicle_Detail.model, Rental_Agreement.date_start, Rental_Agreement.date_return
    FROM Vehicle_Detail INNER JOIN Rental_Agreement ON Vehicle_Detail.vehicle_no = Rental_Agreement.vehicle_no
    WHERE (((Vehicle_Detail.outlet_no)=[Outlet?eg Ballincollig,Ballingarry,Mahon,Cork]) AND ((Date() < Rental_Agreement.date_start OR Date() > Rental_Agreement.date_return)));

    But it keeps bringing up all of the dates!
    Last Dates question i promise,
    Thanks a mill if you can help!!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    WHERE ...
    Date() < Rental_Agreement.date_start OR
    Date() > Rental_Agreement.date_return

    so, you want the agreement start date somewhere in the future, OR you want the agreement return date somewhere in the past?

    if you change the direction of those inequalities, and change the OR to AND, you might get what you really want


    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Posts
    41
    Ya thats Exactly what i want! Ive tried changing them all but it just doesnt seem to work!!

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, i'll walk you through it

    first of all, don't use DISTINCT

    remove the conditions on the dates, leaving just

    WHERE Vehicle_Detail.outlet_no = [Outlet?eg Ballincollig,Ballingarry,Mahon,Cork]

    this should produce more rows than you want

    now show me some of the sample rows returned, indicate which ones you want, and explain in english what the date criteria are
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2004
    Posts
    41
    make model date_start date_return
    Citreon Saxa 15-Feb-04 17-Feb-04
    Mercedes E200 05-Apr-03 12-Apr-03
    Mercedes E200 30-Apr-03 04-May-03
    Mercedes E200 14-Oct-03 21-Oct-03
    Mercedes E200 27-Jan-04 05-Feb-04

    That was the result of taking away all the date conditions! I dont want the merc to appear because it is not available to rent now!i.e it is rented from the 27th - 5th

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you don't want the agreement currently in effect to appear, or you don't want any of the 4 merc rows to appear because it is currently rented?

    and if it's the latter, why do you care about past rentals?

    and why would you want the citroen to show, if it's in the future?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2004
    Posts
    41
    I want the citroen to appear because it is currently available to be rented. I dont want the merc to show because it is currently rented, the dates in the past dont matter but the problem is that the database is reading that the merc is four different cars so when i put in the conditions for the dates its saying that the merc is available ( because the past records are being read as seperate cars)!

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, you just want available cars, not agreements

    that's easy

    select the cars which do not have a current agreement:
    Code:
    select make
         , model
      from Vehicle_Detail 
     where outlet_no = [Outlet?eg Ballincollig,Ballingarry,Mahon,Cork]
       and not exists
           ( select 1 
               from Rental_Agreement 
              where vehicle_no = Vehicle_Detail.vehicle_no
                and date_start <= Date()
                and Date() <= date_return 
           )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Feb 2004
    Posts
    41
    Ah no, that didnt work either - Its still Saying that the merc is avialable!!!
    Dont suppose you have any other ideas?!!

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ok, i figured it out, at least in an abbreviated test version which i tested myself as follows:
    Code:
    create table Vehicle_Detail
    ( make varchar(10)
    , model varchar(10)
    , date_start  datetime
    , date_return  datetime
    )
    
    insert into Vehicle_Detail values ('Citreon','Saxa',#15-Feb-04#,#17-Feb-04#)
    insert into Vehicle_Detail values ('Mercedes','E200',#05-Apr-03#,#12-Apr-03#)
    insert into Vehicle_Detail values ('Mercedes','E200',#30-Apr-03#,#04-May-03#)
    insert into Vehicle_Detail values ('Mercedes','E200',#14-Oct-03#,#21-Oct-03#)
    insert into Vehicle_Detail values ('Mercedes','E200',#27-Jan-04#,#05-Feb-04#)
    
    select * from Vehicle_Detail 
    
    make      model  date_start  date_return
    Citreon   Saxa   2004-02-15  2004-02-17
    Mercedes  E200   2003-04-05  2003-04-12
    Mercedes  E200   2003-04-30  2003-05-04
    Mercedes  E200   2003-10-14  2003-10-21
    Mercedes  E200   2004-01-27  2004-02-05
    
    select make
         , model
         , date_start
         , iif(date_start <= Date(), 1,0)  as s
         , date_return
         , iif(Date() <= date_return , 1,0) as r
         , iif( exists
           ( select 1 
               from Vehicle_Detail 
              where make = AA.make
                and model = AA.model
                and date_start <= Date()
                and Date() <= date_return 
           ), 0, 1 ) as notexists
      from Vehicle_Detail AA
    
    make    model  date_start  s  date_return r  notexists
    Citreon  Saxa  2004-02-15  0  2004-02-17  1  1
    Mercedes E200  2003-04-05  1  2003-04-12  0  0
    Mercedes E200  2003-04-30  1  2003-05-04  0  0
    Mercedes E200  2003-10-14  1  2003-10-21  0  0
    Mercedes E200  2004-01-27  1  2004-02-05  1  0
    so it works for me, and if it doesn't work for you, i dunno what to tell you

    perhaps the problems can be traced back to improperly qualified columns -- after all, i tested on one table, and you have two

    try running the above query on your Rental_Agreement table, assuming that is where the date fields are
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Feb 2004
    Posts
    41
    Ya thats perfect, Thanks!!

Posting Permissions

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