Professional Web Applications Themes

How would I construct this query - MySQL

Hey ya'll, I'm having a bit of difficulty figuring out how to run this query in a way that users won't have gray hair by the time it actually finishes. Here's my setup: (Very) basically, I'm setting up a gear rental system, so I'm keeping track of (a) all of my gear in one table ("gear") and (b) the gear reservations in a second table ("reservations"). Both have a "gearid" field. "reservations" also has two date fields, "checkoutdate" and "returndate". Okay, now here's what I'm trying to do: given a certain time period, how can I find all gear that ...

  1. #1

    Default How would I construct this query

    Hey ya'll, I'm having a bit of difficulty figuring out how to run this
    query in a way that users won't have gray hair by the time it actually
    finishes. Here's my setup:

    (Very) basically, I'm setting up a gear rental system, so I'm keeping
    track of (a) all of my gear in one table ("gear") and (b) the gear
    reservations in a second table ("reservations"). Both have a "gearid"
    field. "reservations" also has two date fields, "checkoutdate" and
    "returndate". Okay, now here's what I'm trying to do: given a certain
    time period, how can I find all gear that is NOT already reserved for
    that time period?

    It seems like there should be a way to do it using left joins and
    SQL's handy-dandy date functions, but the only thing I've come up with
    so far is to construct a separate query for each and every piece of
    gear to check if it's reserved; which, when you're keeping track of
    several thousand items, seems laughably inefficient.

    Any advice is very welcome!!

    danep Guest

  2. #2

    Default Re: How would I construct this query

    On 9 Feb, 07:37, "danep" <com> wrote: 

    Before I answer, I need to clarify something:
    You say "given a certain time period, how can I find all gear that is
    NOT already reserved for that time period?"
    Does that mean gear that is not reserved for any part of that period,
    or is not available for the whole of that period and are the endpoints
    in or out?

    e.g. period = 2007-02-20 to 2007-03-10
    So:
    if a piece of equipment is reserved for 2007-02-18 to 2007-02-21 is it
    in or out?
    if a piece of equipment is reserved for 2007-02-18 to 2007-02-20 is it
    in or out?
    if a piece of equipment is reserved for 2007-03-10 to 2007-03-15 is it
    in or out?
    if a piece of equipment is reserved for 2007-02-25 to 2007-02-26 is it
    in or out?





    Captain Guest

  3. #3

    Default Re: How would I construct this query

    On Feb 9, 3:43 am, "Captain Paralytic" <com> wrote: 



    >
    > Before I answer, I need to clarify something:
    > You say "given a certain time period, how can I find all gear that is
    > NOT already reserved for that time period?"
    > Does that mean gear that is not reserved for any part of that period,
    > or is not available for the whole of that period and are the endpoints
    > in or out?
    >
    > e.g. period = 2007-02-20 to 2007-03-10
    > So:
    > if a piece of equipment is reserved for 2007-02-18 to 2007-02-21 is it
    > in or out?
    > if a piece of equipment is reserved for 2007-02-18 to 2007-02-20 is it
    > in or out?
    > if a piece of equipment is reserved for 2007-03-10 to 2007-03-15 is it
    > in or out?
    > if a piece of equipment is reserved for 2007-02-25 to 2007-02-26 is it
    > in or out?[/ref]

    I want to find gear that is not reserved for any part of that period,
    including the endpoints. In other words, the piece of gear must be
    completely available for the entire time period given, including the
    endpoints. I would be very grateful (not to mention impressed!) if
    you could help me out, thanks!

    danep Guest

  4. #4

    Default Re: How would I construct this query

    On Fri, 09 Feb 2007 17:22:47 +0100, danep <com> wrote:
     


    Untested:

    SELECT g.`gearid`
    FROM `gear` g
    WHERE g.`gear`id` NOT IN (
    SELECT DISTINCT r.`gearid`
    FROM `reservations` r
    WHERE
    (r.`checkoutdate` BETWEEN 'your_start_date' AND 'you_end_date')
    OR
    (r.`returndate` BETWEEN 'your_start_date' AND 'you_end_date')
    )


    --
    Rik Wasmus
    Rik Guest

  5. #5

    Default Re: How would I construct this query

    On Feb 9, 10:53 am, Rik <com> wrote: 
    >
    > Untested:
    >
    > SELECT g.`gearid`
    > FROM `gear` g
    > WHERE g.`gear`id` NOT IN (
    > SELECT DISTINCT r.`gearid`
    > FROM `reservations` r
    > WHERE
    > (r.`checkoutdate` BETWEEN 'your_start_date' AND 'you_end_date')
    > OR
    > (r.`returndate` BETWEEN 'your_start_date' AND 'you_end_date')
    > )
    >
    > --
    > Rik Wasmus[/ref]

    That solves half of it, namely how to correlate the gear table to the
    reservations table.... however what if gear is already reserved for
    ALL of the time period you are interested in, and then some... Then
    the "checkoutdate" would fall before your start date and the
    "returndate" would fall after your end date, and neither of the
    between conditions would apply. What if you make the WHERE clause

    WHERE g.`gear`id` NOT IN (
    SELECT DISTINCT r.`gearid`
    FROM `reservations` r
    WHERE
    (r.`checkoutdate` BETWEEN 'your_start_date' AND 'you_end_date')
    OR
    (r.`returndate` BETWEEN 'your_start_date' AND 'you_end_date')
    OR
    ('your_start_date' BETWEEN r.`checkoutdate` AND r.`returndate`)
    OR
    ('your_end_date' BETWEEN r.`checkoutdate` AND r.`returndate`)

    That *seems* like it should work, doesn't it? Thanks for your help!

    danep Guest

  6. #6

    Default Re: How would I construct this query

    danep wrote: 
    >>
    >> Untested:
    >>
    >> SELECT g.`gearid`
    >> FROM `gear` g
    >> WHERE g.`gear`id` NOT IN (
    >> SELECT DISTINCT r.`gearid`
    >> FROM `reservations` r
    >> WHERE
    >> (r.`checkoutdate` BETWEEN 'your_start_date' AND
    >> 'you_end_date') OR
    >> (r.`returndate` BETWEEN 'your_start_date' AND 'you_end_date')
    >> )
    >>
    >> --
    >> Rik Wasmus[/ref]
    >
    > That solves half of it, namely how to correlate the gear table to the
    > reservations table.... however what if gear is already reserved for
    > ALL of the time period you are interested in, and then some... Then
    > the "checkoutdate" would fall before your start date and the
    > "returndate" would fall after your end date, and neither of the
    > between conditions would apply. What if you make the WHERE clause
    >
    > WHERE g.`gear`id` NOT IN (
    > SELECT DISTINCT r.`gearid`
    > FROM `reservations` r
    > WHERE
    > (r.`checkoutdate` BETWEEN 'your_start_date' AND 'you_end_date')
    > OR
    > (r.`returndate` BETWEEN 'your_start_date' AND 'you_end_date')
    > OR
    > ('your_start_date' BETWEEN r.`checkoutdate` AND r.`returndate`)
    > OR
    > ('your_end_date' BETWEEN r.`checkoutdate` AND r.`returndate`)
    >
    > That *seems* like it should work, doesn't it? Thanks for your help![/ref]

    Rik's query is rather wrong and way more complicated than it needs to be I'm
    afraid, as I guess you found out.

    It is far simpler than that.

    SELECT g.*
    FROM gear g
    LEFT JOIN reservations r ON r.gearid = g.gearid
    AND r.checkoutdate <= 'periodend'
    AND r.returndate >= 'periodstart'
    WHERE r.gearid IS NULL


    Paul Guest

  7. #7

    Default Re: How would I construct this query

    On Feb 9, 11:38 am, "Paul Lautman" <com>
    wrote: [/ref]
    > [/ref]
    > [/ref]
    > [/ref]



    >
    > Rik's query is rather wrong and way more complicated than it needs to be I'm
    > afraid, as I guess you found out.
    >
    > It is far simpler than that.
    >
    > SELECT g.*
    > FROM gear g
    > LEFT JOIN reservations r ON r.gearid = g.gearid
    > AND r.checkoutdate <= 'periodend'
    > AND r.returndate >= 'periodstart'
    > WHERE r.gearid IS NULL[/ref]

    That seems very close; however what if the gear has TWO existing
    reservations, one of which conflicts with the desired time period and
    one of which doesn't. Then (as far as I can tell) this query would
    still return the gear as available. Does that make sense?

    danep Guest

  8. #8

    Default Re: How would I construct this query

    On Feb 9, 12:27 pm, "danep" <com> wrote: [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]



    >
    > That seems very close; however what if the gear has TWO existing
    > reservations, one of which conflicts with the desired time period and
    > one of which doesn't. Then (as far as I can tell) this query would
    > still return the gear as available. Does that make sense?[/ref]

    Whoops, I take it back... I'm having a very difficult time sorting
    through this in my head, but on second thought I think this query
    should work after all. I guess the way to find out would just be to
    try it :)

    danep Guest

  9. #9

    Default Re: How would I construct this query

    Paul Lautman wrote: 

    Yup, it's wrong, my apologies. That'll teach me to post untested queries
    :P. More complicated then need be is another question.
     

    Still wouldn't catch all of it. The way I figure is there are three
    options to be unavailable:
    1. checkoudate is between dates
    2. returndate is between dates
    3. checkoutdate is before startdate && returndate is after enddate

    SELECT g.*
    FROM `gear` g
    LEFT JOIN `reservations`
    ON r.`gearid` = g.`gearid`
    AND
    (
    r.`checkoutdate` BETWEEN 'periodstart' AND 'periodend'
    OR
    r.`returndate` BETWEEN 'periodstart' AND 'periodend'
    OR
    (
    r.`checkoutdate` <= 'periodstart'
    AND
    r.`returndate` >= 'periodend'
    )
    )
    WHERE r.`gearid` IS NULL
    --
    Rik Wasmus
    Rik Guest

  10. #10

    Default Re: How would I construct this query

    Forget that.

    Let's just say I'm having a very bad sql-day...

    --
    Rik Wasmus
    Rik Guest

  11. #11

    Default Re: How would I construct this query

    danep wrote: 
    >> 
    >> 
    >> 
    >> 
    >> 
    >> 
    >> 
    >> 
    >> 
    >>
    >> That seems very close; however what if the gear has TWO existing
    >> reservations, one of which conflicts with the desired time period and
    >> one of which doesn't. Then (as far as I can tell) this query would
    >> still return the gear as available. Does that make sense?[/ref]
    >
    > Whoops, I take it back... I'm having a very difficult time sorting
    > through this in my head, but on second thought I think this query
    > should work after all. I guess the way to find out would just be to
    > try it :)[/ref]

    Yep, I promise you, I've used this sort of query many times.
    It LOOKS too simple to do the job, but in fact it is all that is needed!


    Paul Guest

Similar Threads

  1. tell me the best way to re-construct this menu!
    By jeffdoe webforumsuser@macromedia.com in forum Macromedia Flash Actionscript
    Replies: 1
    Last Post: February 6th, 01:38 AM
  2. how to construct 2 consecutive if conditions?
    By Danield in forum PERL Beginners
    Replies: 1
    Last Post: January 25th, 05:43 AM
  3. failing elseif construct
    By Bert in forum PHP Development
    Replies: 5
    Last Post: August 12th, 08:44 PM
  4. SQL construct not supported in informix ?
    By Art S. Kagel in forum Informix
    Replies: 6
    Last Post: July 25th, 01:21 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139