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!!

On 9 Feb, 07:37, "danep" <danepow...@gmail.com> wrote:
> 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!!

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?

On Feb 9, 3:43 am, "Captain Paralytic" <paul_laut...@yahoo.com> wrote:
> On 9 Feb, 07:37, "danep" <danepow...@gmail.com> wrote:
>
>
>
> > 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!!
>
> 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?

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!

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

> 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!


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

On Feb 9, 10:53 am, Rik <luiheidsgoe...@hotmail.com> wrote:
> On Fri, 09 Feb 2007 17:22:47 +0100, danep <danepow...@gmail.com> wrote:
> > 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!
>
> 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

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 wrote:
> On Feb 9, 10:53 am, Rik <luiheidsgoe...@hotmail.com> wrote:
>> On Fri, 09 Feb 2007 17:22:47 +0100, danep <danepow...@gmail.com>
>> wrote:
>>> 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!
>>
>> 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
>
> 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!

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

On Feb 9, 11:38 am, "Paul Lautman" <paul.laut...@btinternet.com>
wrote:
> danep wrote:
> > On Feb 9, 10:53 am, Rik <luiheidsgoe...@hotmail.com> wrote:
> >> On Fri, 09 Feb 2007 17:22:47 +0100, danep <danepow...@gmail.com>
> >> wrote:
> >>> 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!
>
> >> 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
>
> > 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!
>
> 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

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?

On Feb 9, 12:27 pm, "danep" <danepow...@gmail.com> wrote:
> On Feb 9, 11:38 am, "Paul Lautman" <paul.laut...@btinternet.com>
> wrote:
>
>
>
> > danep wrote:
> > > On Feb 9, 10:53 am, Rik <luiheidsgoe...@hotmail.com> wrote:
> > >> On Fri, 09 Feb 2007 17:22:47 +0100, danep <danepow...@gmail.com>
> > >> wrote:
> > >>> 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!
>
> > >> 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
>
> > > 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!
>
> > 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
>
> 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?

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

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

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

> 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

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

Forget that.

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

--
Rik Wasmus