[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2005-04-22
8
Medium Priority
?
225 Views
Last Modified: 2010-05-18
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
0
Comment
Question by:Cardguy
  • 3
  • 2
  • 2
7 Comments
 
LVL 5

Expert Comment

by:obahat
ID: 13845433
DECLARE @NewRequestTime DATETIME -- time requested for a laptop reservation

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

Author Comment

by:Cardguy
ID: 13845452
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
 
LVL 5

Expert Comment

by:obahat
ID: 13845537
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:Cardguy
ID: 13845838
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
 
LVL 17

Accepted Solution

by:
BillAn1 earned 1500 total points
ID: 13845893
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
 
LVL 5

Expert Comment

by:obahat
ID: 13845916
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
 
LVL 17

Expert Comment

by:BillAn1
ID: 13845940
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

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

830 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