Cardguy
asked on
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
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
ASKER
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?
Also, don't I have to join the two tables to return the laptopIDs that aren't assigned?
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).
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).
ASKER
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
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_da
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...
BTW, the logic may look wrong, but basically you are testing for overlap between the RequestDate->ReturnDate period and the #request_date#->#return_da
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...
SELECT LaptopID
FROM LaptopRequests
WHERE @NewRequestTime NOT BETWEEN RequestDate AND ReturnDate