Re: Availability between dates

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

The interval [start, end] does not overlap at all the interval [fromThis,
toThis] if, and only if


fromThis > end OR toThis < start


and there is some overlap (partial or total) with the negation of the
previous statement, or if you prefer, if:


fromThis <= end AND toThis >= start


(using the so called De Morgan's law)



So, a possible solution is to check all items with a possible overlap, then,
make a second query which will look at all the items not in the first set.
The first query is thus:

SELECT itemID
FROM table2
WHERE hireoutFrom <= [periodEnd] AND hireoutUntil >= [periodStart]


which returns items 'hired' within (some part of) the period
[periodStart], [periodEnd] . Assume you save this query as q1. The second
and final query is:



SELECT itemID
FROM table1 LEFT JOIN q1
ON table1.itemID = q1.itemID
WHERE category = [wantedCategory]
AND q1.itemID IS NULL


would return the items not hired for any part of the said period, said
[wantedCategory].






Vanderghast, Access MVP




"Eddie Holder" <eddiesanet@xxxxxxxxxxx> wrote in message
news:C550B019-858C-44F6-9F68-7081CE1A5641@xxxxxxxxxxxxxxxx
Hi Guys - I hope someone can help. I have a database that hires out
products,
and need help to try and find which products are available for hire
between
date A and data b. Here it goes

I have a database with 2 tables.
Table 1 - this holds product information with each product having a unique
ID and belonging to a category, e.g. seats, chairs, tables, etc

Table 2 - This holds all the hired out products and the date from and the
date to when it's hired out, e.g. Product 1, in seats category is hired
out
from the 01/01/09 to the 15/01/09

When I have a request for a new hire, I would like to be able to run a
query
that will return which products can be hired based on the hire request
date
from and request date to, e.g. I would like to hire a seat from the
10/01/09
to the 13/01/09.

As we will have multiple products in each category, I would like to search
by category to find which products are available. e.g. show me all seat
products that can be hired on the hire request date.

Any advice or help will be appreciated.


Thank you in advanced!
Eddie


.

Relevant Pages

  • Re: Availability between dates
    ... So, a possible solution is to check all items with a possible overlap, then, ... make a second query which will look at all the items not in the first set. ... ID and belonging to a category, e.g. seats, chairs, tables, etc ... that will return which products can be hired based on the hire request ...
    (microsoft.public.access.queries)
  • RE: Availability between dates
    ... You might find the following query helpful as a model. ... AND (CalDate <= DepartureDate OR DepartureDate IS NULL) ... ID and belonging to a category, e.g. seats, chairs, tables, etc ... When I have a request for a new hire, I would like to be able to run a query ...
    (microsoft.public.access.queries)
  • Re: [KVM PATCH v9 2/2] KVM: add iosignalfd support
    ... previously I've been too confused with complicated locking to notice ... this checks both region overlap and data collision. ... Note that "false" means we are accepting the request, ...
    (Linux-Kernel)
  • Re: about TRIM/DISCARD support and barriers
    ... I just mean writes _to the same sector_. ... My main worry is that this will add considerable overhead to request ... For the rbtree based sorting, we'd have to do a rb_next/rb_prev ... overlap checking if one of the requests is a discard. ...
    (Linux-Kernel)
  • Re: dynamic structure for storing/querying intervals
    ... intervals on a line (or, in possibly degenerate cases, just points). ... I'd like to be able to query the structure by providing a range (again, ... that when many intervals overlap the performance degrades significantly. ... Each node also contains the maximum endpoint ...
    (comp.programming)