Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2011
    Posts
    4

    Unanswered: Available date range

    hi all,

    I'm putting together a small reservation system for equipment and I'm a little stuck on finding which equipment does NOT have a reservation between two specified dates.

    My reservations table is as follows:
    event_id -- integer
    equipment_id -- integer
    event_start -- datetime
    event_end -- datetime

    Basically I'll create reservations and specify the above information. What I need to be able to do is do a search for which equipment_ids that do NOT event(s) that fall between a specified FROM and TO date range in my search criteria.

    e.g. equipment ID 555 has a reservation event starting 10/10/2011 and ending 10/20/2011. If I specify my available equipment searching criteria, FROM 10/15/2011 and TO 10/25/2011 then equipment ID 555 should NOT show up in the results as it is not available during this time period.

    I found the following post: http://www.dbforums.com/microsoft-sq...er-2005-a.html BUT! when I utilize the query from the link inside that post... it returns me the equipment that DOES fall between a specified FROM/TO date range??? which is basically the opposite of what I'm looking for...

    So, in addition, I realized that this reservations table only contained equipment IDs that actually have reservations, so I'll need to join this with another table that contains a list of the reservable equipment IDs, that way assets without reservations will be returned in the query as well.. might need a tip on this part in conjunction with what I mentioned above.

    I was thinking this was going to be some sort of simple date between query of some sort but I couldn't get that to work, so it's a bit beyond me at this point. Any help would be appreciated. If you need me to clarify further just let me know.

    Thanks!

    -Drew
    Last edited by Drewster727; 10-09-11 at 15:37.

  2. #2
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Perhaps if it is doing exactly the opposite of what you want, they you simply need to negate the criteria.

    If you have a WHERE clause like this:

    WHERE a>=b and c<=d

    and it is coming up with the opposite results that you are looking for, then adjusting the WHERE clause to look like this:

    WHERE not (a>=b and c<=D)

    would work.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  3. #3
    Join Date
    Oct 2011
    Posts
    4
    Thanks for the reply. Still having issues with this one, I have the following query made up, but it's still returning asset tags (aka equipment IDs) that have events that span across the specified range:

    Code:
    SELECT ca.[Asset Tag]
    FROM CheckoutAssets ca LEFT OUTER JOIN
    	(SELECT DISTINCT rv.[Asset Tag] from CheckoutAssets_ReservationsView rv where NOT
    	(rv.event_end >= '10/16/2011'
    	and rv.event_start <= '10/19/2011')) as rvt on ca.[Asset Tag] = rvt.[Asset Tag]
    WHERE ca.Reservable = 'True' and ca.Type = 'Laptop'
    so that returns me a list of asset tags that are reservable and is of type 'Laptop', but it still includes the tags that have reservations that span across the 10/16/2011 - 10/19/2011 date range... can anyone tell me what I'm doing wrong with this one? Thanks in advance.
    Last edited by Drewster727; 10-09-11 at 16:30.

  4. #4
    Join Date
    Oct 2011
    Posts
    4
    I think I may have got it...

    Code:
    SELECT ca.[Asset Tag]
    FROM CheckoutAssets ca 
    WHERE ca.Reservable = 'True' and ca.Type = 'Laptop' and not exists
    	(SELECT rv.[Asset Tag] from CheckoutAssets_ReservationsView rv where
    	 ca.[Asset Tag] = rv.[Asset Tag] and
    	(rv.event_end >= '10/16/2011'
    	and rv.event_start <= '10/19/2011'))
    guess I just needed to talk it out, haha. If you guys know of a more efficient method I'm all ears! thx!

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I tried to solve your problem. A bug prevented me to post it earlier (then I watched a new episode of "House"). Finally, when I wanted to submit it, I saw you had already solved it yourself!

    My solution uses a left outer join instead of your EXISTS. Both give the same result. Comparing the execution plans of both solutions, gave the same estimated execution cost for both. The only thing I changed in your code is replacing
    SELECT rv.[Asset Tag] from CheckoutAssets_ReservationsView
    by
    SELECT 1 from CheckoutAssets_ReservationsView

    Perhaps this is just an old habit. The estimated execution cost with "rv.[Asset Tag]" or "1", stayed the same.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  6. #6
    Join Date
    Oct 2011
    Posts
    4
    hey thanks for the reply. I appreciate the input on it, I'll make that adjustment to the select sub-query. thanks!!

Posting Permissions

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