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

    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: 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.


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

  2. #2
    Join Date
    Sep 2001
    Chicago, Illinois, USA
    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.

    Maverick Software Design

    (847) 864-3600 x2

  3. #3
    Join Date
    Oct 2011
    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:

    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
    I think I may have got it...

    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
    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
    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

    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
    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