how to find if a date range not in a table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

how to find if a date range not in a table

Author

Topic

mtnlvy
Starting Member

8 Posts

Posted - 2011-06-07 : 02:33:02

i have two tables
* rooms( numroom, datas about the rom)
* orders ( numroom , date_begin, date_end)
the tsble of the orders can include a lot of date ranges for the same room for example
room date_begin date_end
2 7/6/2011 9/6/2011
2 16/6/2011 7/7/2011
2 8/7/2011 9/7/2011
1 7/6/2011 10/6/2011

i have to find if specific date range is not in the date ranges
for example 10/6/2011 to 12/6/2011 is not in the table of the date ranges for room number 2
13/6/2011 to 16/6/2011 is not ok because it found in the ranges of the order table.
i need to build a query that give me all the room numbers for one date range
for example i have rooms number 1, 2, 3 the date range given is 9/9/2011 to 15/9/2011 the query result is room number 1, 2,3
if the date range given is 7/6/2011 to 10/6/2011 only room number 3 will apear and if the date range given is 10/6/2011 to 12/6/2011
room number 1 and 3 will be given
thank you very much

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-06-07 : 03:26:41

SELECT * FROM dbo.Orders
WHERE NOT (DateBegin <= @WantedToDate AND DateEnd >= @WantedFromDate)

N 56°04'39.26"
E 12°55'05.63"

mtnlvy
Starting Member

8 Posts

Posted - 2011-06-07 : 05:15:17

thank you but it's not working. may i didn't explain myself well
1. i've the orders list that give me the list of date ranges where the room is not available for a new order and i've to find if the room is available for the date range i gave
2. if the room is not in the order list it mean that the room is available so it have to be in the list of rooms available that the query will give so i can't use only the orders table i must do any join with the rooms table.
thank you

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-07 : 09:00:06

You may need something like this - I am not sure if my where conditions in the exist clause cover all possibilities, so that may need to be expanded:


DECLARE @date_begin date, @date_end date;
SET @date_begin = '7/6/2011'; SET @date_end = '10/6/2011';

SELECT room FROM rooms r
WHERE NOT EXISTS
(
   SELECT * 
   FROM orders o
   WHERE r.room = o.room AND
   (
      @date_begin BETWEEN o.date_begin AND o.date_end
      OR 
      @date_end BETWEEN o.date_begin AND o.date_end
      OR
      (@date_begin <= o.date_begin AND @date_end >= o.date_end)
   )
);

mtnlvy
Starting Member

8 Posts

Posted - 2011-06-07 : 09:37:13

it still not working ... but i've to think about how to improve it
thank you

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-06-07 : 12:53:41

SELECT Num_Room FROM Orders
GROUP BY Num_Room
HAVING MAX(CASE WHEN Date_Begin <= @WantedToDate AND Date_End >= @WantedFromDate THEN 1 ELSE 0 END) = 0

N 56°04'39.26"
E 12°55'05.63"

mtnlvy
Starting Member

8 Posts

Posted - 2011-06-09 : 01:48:05

it not seems to be working.
i''l give an simple numeric example:
begin end

1 3
5 7
10 13
16 20

the range 8 to 9 will be OK but 7 to 9 will not be OK 8 to 11 is not ok ,21 to 24 is ok

secondly - if a room not found in the orders table any range will be OK.
thank you

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-06-09 : 02:08:06

[code]DECLARE @Rooms TABLE
(
Room INT
)

INSERT @Rooms
VALUES (1),
(2),
(3)

DECLARE @Orders TABLE
(
Room INT,
DateBegin DATETIME,
DateEnd DATETIME
)

SET DATEFORMAT DMY

INSERT @Orders
VALUES (2, '7/6/2011', '9/6/2011'),
(2, '16/6/2011', '7/7/2011'),
(2, '8/7/2011', '9/7/2011'),
(1, '7/6/2011', '10/6/2011')

/*
Solution starts here
*/
-- Mimic user supplied parameters
DECLARE @FromDate DATETIME = '7/6/2011',
@ToDate DATETIME = '10/6/2011'

-- Display the result
SELECT Room
FROM @Rooms

EXCEPT

SELECT Room
FROM @Orders
WHERE DateBegin <= @ToDate
AND DateEnd >= @FromDate
GROUP BY Room[/code]

N 56°04'39.26"
E 12°55'05.63"

mtnlvy
Starting Member

8 Posts

Posted - 2011-06-09 : 09:15:50

Thank you very much
it's working!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources