Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2009
    Posts
    2

    Unanswered: car reservation when are all cars reserverd

    Hi,

    i have a car reservation table in my db

    the fields we use are id, carid, startdate, endddate, user,..

    the startdate and endddate are datetime


    now i wanna know with cars are rented at the same time,

    an example let say we have 3 cars you can reserve

    car1 is reserved from startdate 2009-10-01 09.00 and enddate 2009-10-01 10.00
    car2 is reserved from startdate 2009-10-01 09.20 and enddate 2009-10-01 11.00
    car3 is reserved from startdate 2009-10-01 10.00 and enddate 2009-10-01 10.45
    car1 is reserved from startdate 2009-10-01 12.00 and enddate 2009-10-01 12.30



    so how can i see the date and time that all my (3) cars ar rented,

    and this on daily,monthy,..

    Tnx

  2. #2
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    Select *
    From [car reservation table]

    that will return everything for all cars

    but i don't think that is that you mean

    if you mean how can you tell which cars are reserved in a time frame then you do

    Select *
    From [car reservation table]
    where startdate between @TimeFrameStart and @TimeFrameEnd
    or endddate between @TimeFrameStart and @TimeFrameEnd

    if you want to find conflicts then the following should do the trick

    Select *
    From [car reservation table] as r1 inner join [car reservation table] as r2
    on r1.carid = r2.carid
    where ( r1.startdate between @TimeFrameStart and @TimeFrameEnd
    or r1.endddate between @TimeFrameStart and @TimeFrameEnd)
    AND
    ( r1.startdate between r2.startdate and r2.endddate
    or r1.endddate between r2.startdate and r2.endddate)

    if you mean how can you tell which cars aren't reserved in a time frame then you do

    Select Distinct c.carid
    From [car table] as c right outer join
    (Select *
    From [car reservation table]
    where startdate between @TimeFrameStart and @TimeFrameEnd
    or endddate between @TimeFrameStart and @TimeFrameEnd) as cr
    on c.carid = cr.carid
    where cr.carid is null


    if none of them is what you mean then can you give more details
    Last edited by m.timoney; 10-28-09 at 14:29.
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  3. #3
    Join Date
    Oct 2009
    Posts
    2

    @TimeFrameStart and @TimeFrameEnd?

    What you mean with @TimeFrameStart and @TimeFrameEnd

    ?


    TNx

    I am just a beginner

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by stineco

    I am just a beginner
    .. with a tough homework assignment

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

  5. #5
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Or a developer at Hertz.

  6. #6
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    Quote Originally Posted by r937 View Post
    .. with a tough homework assignment

    i'd have to agree
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

Posting Permissions

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