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

I have a two table setup:

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

Laptops
    LaptopID
    SerialNumber

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?

Thanks
CardguyAsked:
Who is Participating?
 
BillAn1Connect With a Mentor Commented:
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")#>

<CFQUERY DATASOURCE="xxx" NAME="yyy">
select laptopID, SerialNumber
from Laptops
where LaptopID not in (
      select LaptopID from LaptopRequests
      where #return_date# >= RequestDate
        AND   #request_date# <= ReturnDate
)
</CFQUERY>
0
 
obahatCommented:
DECLARE @NewRequestTime DATETIME -- time requested for a laptop reservation

SELECT LaptopID
FROM LaptopRequests
WHERE @NewRequestTime  NOT BETWEEN RequestDate AND ReturnDate
0
 
CardguyAuthor Commented:
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?
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
obahatCommented:
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).
0
 
CardguyAuthor Commented:
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?
0
 
obahatCommented:
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.
0
 
BillAn1Commented:
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...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.