Returning a specific subset from two linked tables based on date range

Posted on 2005-04-22
Last Modified: 2010-05-18
I have a two table setup:

    Status  (A for Open, B for Assigned, O for Out of Office, R for Returned)
    LaptopID (linked to table below)


Basically, users will be able to enter requests into the database.  We then choose a request and assign an available laptop.  So, once I choose a request, I need a SQL query to only return any laptops that are available for that time period (meaning they have not been assigned or are not out of the office).  I have no clue where to begin on this one.  I have some ideas that would work in Cold Fusion.  However, I would like to try and do this completely with a SQL query that only returns the LaptopIDs that have not been assigned for that time period.

Is this possible?  Or, do I need to change my table structure?

Question by:Cardguy
    LVL 5

    Expert Comment

    DECLARE @NewRequestTime DATETIME -- time requested for a laptop reservation

    SELECT LaptopID
    FROM LaptopRequests
    WHERE @NewRequestTime  NOT BETWEEN RequestDate AND ReturnDate

    Author Comment

    I'm a bit of a newbie in SQ....sorry.  How do I assign the value of that variable?

    Also, don't I have to join the two tables to return the laptopIDs that aren't assigned?
    LVL 5

    Expert Comment

    1. Assigning a variable:

    DECLARE @NewRequestTime DATETIME -- this is the variable declaration

    SET @NewRequestTime ='2/3/2004 3:23:56 PM'

    -- the SET command assigns a value, when the value is not returned from a table.
    -- If you want to assign to @NewRequestTime a value from the table, then use
    SELECT @NewRequestTime = RequestDate -- for example
    FROM LaptopRequests
    WHERE RequestNumber = <Desired number>

    2. There is no need to join, since the Laptop ID is already in table LaptopRequests.
    You also mentioned that the column on LaptopRequests is linked to LaptopID (I assume you mean by a foreignkey),
    which means that the entries in LaptopRequests are all valid laptop number entries (in most cases of interest).

    Author Comment

    I'm still a bit confused.  The relationship between the two tables is a one to many.  There are, say, 5 laptops in the rotation.  However, over the course of a year there may be 30 requests.  So, the same LaptopID will be listed multiple times in the requests table.  Also, if there are four requests active on any one day, there will be a 5th laptop available in the Laptop table....that won't be listed in the requests table.  I don't see how your code will find that laptopid that does not exist for that timeframe in the Requests table.

    I think I might have just answered my own question.  Wouldn't this just be a join that shows between the two tables that shows the laptopID from the Laptop table which does not exist in the Requests table for that timeframe?
    LVL 17

    Accepted Solution

    it is not sufficient to check that the request time is not within the period the laptop is checked out. You also need to check the desired return date does not overlap.
    Secondly, presumably a laptop can be checked out more than once. so it may have been checked out at some previous time, but also may be checed out at the desired time too, so the above query won't work.
    and finally, a laptop may never have been checked out, so again the above query won't work.

    what you need is something like this (assign your CF variables as you see fit of course)

    <CFSET request_date = #CREATEODBCDATETIME("4/22/2005")#>
    <CFSET return_date = #CREATEODBCDATETIME("4/24/2005")#>

    select laptopID, SerialNumber
    from Laptops
    where LaptopID not in (
          select LaptopID from LaptopRequests
          where #return_date# >= RequestDate
            AND   #request_date# <= ReturnDate
    LVL 5

    Expert Comment

    Yes. I was under the impression that all laptops would be listed in the LaptopRequests. Sorry.

    You're query would be:

    SELECT LaptopID
    FROM LaptopRequests a LEFT OUTER JOIN Laptops b ON a.LaptopID = b.LaptopID
    WHERE @NewRequestTime  BETWEEN a.RequestDate AND a.ReturnDate
       and b.LaptopID IS NULL

    I would also recommend to revise the table design to a laptops table, a requests table, anad returned/status table.
    LVL 17

    Expert Comment

    sorry I was a bit slow in hitting ht esubmit button, You had already drawn much of the same conclusion as I had. Hope the query helps.
    BTW, the logic may look wrong, but basically you are testing for overlap between the RequestDate->ReturnDate period and the #request_date#->#return_date# period. This only occurs if the #return_date# comes after the RequestDate (i.e. there is no overlap if you want to return it again before it next gets checked out) AND if the #request_date# comes before the ReturnDate (i.e. there is no overlap if you don;t wan to check it out until after it has already been returned)

    The only thing the logic doesn't check, put presumably your CF form will check is that the requested return date aught be after the requested checkout date...

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    In this article—a derivative of my blog post (—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    Via a live example, show how to shrink a transaction log file down to a reasonable size.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now