Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2012
    Posts
    1

    Question Unanswered: Is there a better way to query?

    I have a list of volunteers in one table,
    each volunteer has a unique ID

    Voulnteer.VolunteerID
    Voulnteer.Email
    Voulnteer.FirstName
    Voulnteer.LastName
    Volunteer.Skills

    In another table, I have a list of the date/times
    that each volunteer is booked to help with an event.

    Event.StartDate
    Event.EndDate
    Event.VolunteerID

    What I want to find, are those volunteers that
    are NOT already booked, based upon skills that I am looking for.
    '==============================================
    This what I have....
    Select Volunteer.* From Volunteer Where Volunteer.Skills Like '%Whatever%' AND Volunteer.VolunteerID not in(Select VolunteerID From Events Where (StartDat>=#03/03/14# AND EndDate<=#04/04/14#))

    The real problem, is that the query seems to take a long time. Is there any more efficient means to do this?
    There are index on the three fields in EVENT that I am searching on.
    Last edited by mike032; 05-09-14 at 06:10. Reason: complete/correct question

  2. #2
    Join Date
    Apr 2012
    Posts
    213
    Try:

    Code:
    Select Volunteer.* From Volunteer 
    Where 
        Volunteer.Skills Like '%Whatever%' AND 
        not exists 
            (Select VolunteerID From Events 
             Where 
                 Events.VolunteerID = Volunteer.VolunteerID AND
                 StartDat >= #03/03/14# AND 
                 EndDate <= #04/04/14#)
    Hope this helps.

  3. #3
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. You have no idea! You told use you have only one Volunteer. Temporal data should use ISO-8601 formats. Your weird “#03/03/14#” is not even close. Code should be in Standard SQL as much as possible and not local dialect.

    This is minimal polite behavior on SQL forums.

    >>I have a list of volunteers in one table, each volunteer has a unique ID. <<

    Well, yes; they are entities. This is called the law of identity. Why did you de-normalize your data? A skill is not an attribute; it is part of a relation ship. I want to use the DOT codes for the skills, as guess since you well not post specs. Same logic for events and volunteers.

    CREATE TABLE Volunteers
    (volunteer_id CHAR(10) NOT NULL PRIMARY KEY,
    email VARCHAR(256) NOT NULL,
    first_name VARCHAR(25) NOT NULL,
    last_name first_name VARCHAR(25) NOT NULL);

    CREATE TABLE Volunteer_Skills
    (volunteer_id CHAR(10) NOT NULL
    REFERENCES Volunteers (volunteer_id),
    dot_code CHAR(9) NOT NULL
    REFERENCES Occupational_Titles (dot_code)
    ON UPDATE CASCADE);

    CREATE TABLE Events
    (event_name CHAR(15) NOT NULL PRIMARY KEY,
    event_start_date DATE NOT NULL,
    event_end_date DATE NOT NULL,
    (event_start_date <= event_end_date));

    CREATE TABLE Event_Signups
    (volunteer_id CHAR(10) NOT NULL
    REFERENCES Volunteers (volunteer_id)
    ON DELETE CASCADE,
    event_name CHAR(15) NOT NULL
    REFERENCES Events (event_name)
    ON DELETE CASCADE,
    PRIMARY KEY (volunteer_id, event_name));

    >> What I want to find, are those volunteers that are NOT already booked, based upon skills that I am looking for. <<

    What about overlapping date ranges?

    SELECT V.volunteer_id
    FROM (SELECT volunteer_id
    FROM Event_Signups AS S,
    Events AS E
    WHERE (@search_start_date
    BETWEEN event_start_date AND event_end_date)
    OR (@search_end_date
    BETWEEN event_start_date AND event_end_date))
    AS V
    EXCEPT
    SELECT B.volunteer_id FROM Booked_Volunteers AS B;

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The real problem, is that the query seems to take a long time. Is there any more efficient means to do this?
    I guessed that a reson of taking a long time might be in the predicate
    "Volunteer.Skills Like '%Whatever%'"
    (No index might be effective for such predicate.)

    If my guess was right, please follow the advice of Celko.
    i.e. Remove Skills column from Volunteer table and create a Volunteer_Skills table.

Tags for this Thread

Posting Permissions

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