MS SQL - проблема с запросом — Дайри (бывший Diary.ru)

пятница, 03 декабря 2010

23:48

MS SQL - проблема с запросом

все записи пользователя в сообществе Abigail

All of the love we left behind watching the flashbacks intertwine. I think our lives have just begun.

Народ, помогите, пожалуйста, сформировать запрос.

У меня есть две таблицы: в одной списки всех комнат гостиницы, в другой - информация о посетителях, датах заезда и отъезда и комнатах, которые они занимают. Таблицы связаны ключом RoomID. Дело в том, что могут быть заняты не все комнаты, то есть вторая таблица содержит информацию только о тех комнатах, что заняты.

А теперь смысл запроса: необходимо вывести список всех свободных на заданную дату комнат.
Запрос, который я приведу ниже, выводит список тех комнат, которые заняты, но не на то число, которое я указываю. А нужно, чтобы он выводил список всех свободных комнат плюс те, что заняты, но на другую дату:

ALTER PROCEDURE GetFreeRoomsOnDate
	@Date datetime
AS
BEGIN
	SELECT r.RoomID
	FROM Rooms r JOIN Visitors v ON r.RoomID=v.RoomID
	WHERE
	NOT EXISTS (SELECT RoomID FROM Visitors 
	WHERE @Date BETWEEN v.ArriveDate AND v.DepartDate)
END

Такое ощущение, что запрос полностью игнорирует содержимое таблицы Rooms. Что с этим делать?

@темы: Вопрос, База данных, MS SQL

URL

Черное и белое.... Когда я еду в маршрутке, то у меня м... *** Как всегда - тонкий узор ночных теней по стеклу. Пр... Сегодня у моего друга сын сделал первые шаги. Сам. И эт...

Одной из «прелестей жизни» для многих людей и является им... Давно замечено - жители побережья не любят работать. Ве... Наконец, на www.avp.travel.ru обновили описание региона 1...

Комментарии

04.12.2010 в 13:11

Veshchiy

Homo homini lupus est!... quam qualis sit non novit.

Предлагаю вот такой вариант.

ALTER PROCEDURE GetFreeRoomsOnDate
@Date datetime
AS
BEGIN
SELECT r.RoomID
FROM Rooms r JOIN Visitors v ON r.RoomID=v.RoomID
WHERE
v.RoomID NOT IN (SELECT RoomID FROM Visitors
WHERE @Date BETWEEN v.ArriveDate AND v.DepartDate)
END

Ну и как один из вариантов написать туда условие еще типа "and v.ArriveDate< @Date"

URL

05.12.2010 в 00:23

fess2007

чтобы лучше вам помочь все таки лучше приведите полное описания таблиц с комментариями

URL

05.12.2010 в 00:43

Abigail

All of the love we left behind watching the flashbacks intertwine. I think our lives have just begun.

Veshchiy, увы, он опять выводит только данные из таблицы Visitors.
fess2007, я попробую.

Таблица Visitors.
В ней содержатся следующие столбцы (я напишу только те, что нам будут нужны): ID посетителя, дата заселения посетителя в гостиницу, дата выселения и номер комнаты, которую он занимает.
Вот скриншот инфы, которая забита в таблицу:
читать дальше

Таблица Rooms.
В ней 3 столбца: ID комнаты, тип (обычный или люкс) и стоимость в сутки.
Скриншот таблицы:
читать дальше

Как вы можете видеть, в таблице Visitors записаны не все комнаты, что есть в гостинице, а лишь те, что зарезервированы. А запрос должен выдавать все свободные комнаты, а не только те, что в резерве не попали в указанную нами дату.

URL

05.12.2010 в 19:00

fess2007

т.е. исходя из прочитанного нам нужно
1) выбрать из таблицы Visitors все комнаты которые не заняты на определенную дату
2) добавить к запросу данные из таблицы rooms которых нет в таблице Visitors в поле RoomID

если я сформулировал правильно то первую часть запроса вы уже написали, осталось добавить вторую

union 
select r.id 
from rooms r 
where r.id not in (select distinct v.roomid from visitors v)

и полный запрос будет выглядеть

SELECT r.ID
	FROM Rooms r JOIN Visitors v ON r.ID=v.RoomID
	WHERE
	NOT EXISTS (SELECT RoomID FROM Visitors 
	WHERE @Date BETWEEN v.AD AND v.DD)
	union 
select r.id 
from rooms r 
where r.id not in (select distinct v.roomid from visitors v)

URL

05.12.2010 в 19:14

Abigail

All of the love we left behind watching the flashbacks intertwine. I think our lives have just begun.

fess2007, Боже мой, вы гений! Запрос заработал! Спасибо Вам огромное, вы спасли мой курсовик! :beg:

URL

05.12.2010 в 19:38

ana_grodno

fess2007

URL

05.12.2010 в 20:00

fess2007

Abigail

к сожалению до гениальности мне далеко, а вообще... если ничего не напутал то этот запрос можно написать немного проще


select * from rooms r
left join visitors v on v.roomid=r.id
where (v.AD>@date or v.DD<@date) or (v.AD is null and v.DD is null)

URL

05.12.2010 в 20:18

Abigail

All of the love we left behind watching the flashbacks intertwine. I think our lives have just begun.

fess2007, оооо, этот код мне больше нравится)) спасибо! Все работает как часы! :beg:

URL

05.12.2010 в 20:24

Abigail

All of the love we left behind watching the flashbacks intertwine. I think our lives have just begun.

А, стоп, стоп! Есть одна проблема - если в списке Visitors одна и та же комната записана на несколько дат, то она все равно выводится, даже если одна из дат попадает в указанную дами дату. Как можно решить эту проблему?

URL

05.12.2010 в 20:31

fess2007

приведите пример если можно

это типа если в комнате живут 2 человека с одними и те ми же датами ?

URL

05.12.2010 в 20:39

Abigail

All of the love we left behind watching the flashbacks intertwine. I think our lives have just begun.

fess2007, нет, если два разных человека живут в этой комнате, сначала один с какой-то даты по другую, а потом второй - после того, как первый благополучно выселился.
Вот так например:

URL

05.12.2010 в 21:26

fess2007

туплю под вечер, вроди так.. но если что опять упустил то пишите.

select * from rooms r where r.id not in 
(
    select distinct v.roomid from visitors v 
    where @date between v.AD and v.DD
)

URL

05.12.2010 в 21:49

Abigail

All of the love we left behind watching the flashbacks intertwine. I think our lives have just begun.

fess2007, ммм, а для второго варианта кода это можно реализовать как-то? Потому что я решила использовать его - как оказалось, первый код выводит не то, что нужно.

	
SELECT DISTINCT * FROM Rooms r
LEFT JOIN Visitors v on v.RoomID=r.RoomID
WHERE (v.ArriveDate>@date or v.DepartDate<@date) 
or (v.ArriveDate is null and v.DepartDate is null)

Тут DISTINCT проблему не решил(

URL

05.12.2010 в 21:55

fess2007

а чем тот код что я последним дал не подходит ?

URL

05.12.2010 в 22:01

Abigail

All of the love we left behind watching the flashbacks intertwine. I think our lives have just begun.

fess2007, сначала все казалось правильным, а когда я начала сверять номера комнат, получилось, что код не выводит все комнаты, попавшие в таблицу Visitors.

URL

05.12.2010 в 22:08

fess2007

так давайте все таки определим постановку задачи.
вам нужно чтобы на какую то конкретную дату выдавался список комнат которые свободны, так ?

в каких ситуациях вот этот код не отрабатывает ?

select * from rooms r where r.id not in 
(
    select distinct v.roomid from visitors v 
    where @date between v.AD and v.DD
)

URL

05.12.2010 в 22:31

Abigail

All of the love we left behind watching the flashbacks intertwine. I think our lives have just begun.

fess2007, стоп. Теперь работает как надо О__о
Он даже не выводит комнаты, которые повторяются.
Уфф... ладно, даже знать не хочу, почему раньше это не работало. Спасибо!

Слушайте, у меня тут еще 5 запросов несделанных, если у меня будут вопросы, можно я буду писать вам в личку?

URL

05.12.2010 в 22:47

fess2007

можете попробовать, но сегодня вам повезло что я смог вам помочь, я сейчас в командировке и достаточно плотно занят, на будущее было бы неплохо чтобы высылали сразу свою базу, чтоб не тратить время на создание таблиц и подготовку тестовых данных

URL

05.12.2010 в 23:12

Abigail

All of the love we left behind watching the flashbacks intertwine. I think our lives have just begun.

fess2007, о, спасибо! С пересылкой базы не проблем - все равно курсач, так что боятся за конфиденциальность нет смысла)) а там посмотрим как вам удобнее будет.

И раз уж пока еще вы свободны, может быть найдете время на еще один вопрос? Та же таблица Visitors, те же столбцы. И нужно определить ближайшую дату освобождения номера.

По сути, мы будем сравнивать разницу между сегодняшней датой и DepartDate и выбирать минимальную.
Я предполагаю, что выглядеть это будет примерно так:

ALTER PROCEDURE [dbo].[GetNearestVisitorDepartDate]
AS
BEGIN
DECLARE @Number int
DECLARE @Visitor int
DECLARE @Todaydate date
SELECT @Todaydate = GETDATE()
SELECT @Number=CONVERT(int,(DATEDIFF(DD,
(SELECT DepartDate FROM Visitors WHERE VisitorID=@Visitor),@Todaydate)))
PRINT @Number
END

Но есть одна проблема - @Visitor не задается, он должен перебирать из всех и выбирать оптимум. Вот этот вариант кода:

ALTER PROCEDURE [dbo].[GetNearestVisitorDepartDate]
AS
BEGIN
DECLARE @Number int
DECLARE @Todaydate date
SELECT @Todaydate = GETDATE()
SELECT @Number=MIN(CONVERT(int,(DATEDIFF(DD,
(SELECT DepartDate FROM Visitors),@Todaydate))))
PRINT @Number
END

выдает ошибку "Cannot perform an aggregate function on an expression containing an aggregate or a subquery." А как еще взять минимум у функции DATEDIFF я не знаю.

URL

05.12.2010 в 23:39

fess2007

По сути, мы будем сравнивать разницу между сегодняшней датой и DepartDate и выбирать минимальную.

не все так наверное просто - DATEDIFF в случае если DepartDate меньше чем текущая дата вернет положительное число, этот номер нам тоже подходит или все таки берем только те которые "еще" освободятся а не "уже" освободились

URL

05.12.2010 в 23:50

Abigail

All of the love we left behind watching the flashbacks intertwine. I think our lives have just begun.

fess2007, упс, вы правы. Это я действительно упустила. Тогда изначально придется сделать такое условие: DepartDate >= Getdate() и работать уже только с ними.

URL

05.12.2010 в 23:51

fess2007

т.е. те номера что уже освободились нам не нужны, и мы ищем только те что еще освободятся ?

URL

05.12.2010 в 23:54

fess2007

вроди бы так

DECLARE @Todaydate date
SELECT @Todaydate = GETDATE()

select v.Visitor,v.roomid  from visitors v
right join 
  (select max((DATEDIFF(DD,DD,@Todaydate))) mindays
   from visitors v where (DATEDIFF(DD,DD,@Todaydate))<=0) m on m.mindays=(DATEDIFF(DD,DD,@Todaydate))

определяет комнату и визитера из нее которая освободится раньше всех.

я спать если что пишите, завтра вечером может быть помогу если получиться

URL

05.12.2010 в 23:59

Abigail

All of the love we left behind watching the flashbacks intertwine. I think our lives have just begun.

fess2007, ну да, в спецификации стоит "ближайшая дата освобождения", так что скорее всего нас интересуют именно занятые номера.
По поводу кода: DATEDIFF(DD,DD,@Todaydate) - второе DD - откуда оно берется? SQL Server выдает ошибку.
И еще mindays - что это?

URL

06.12.2010 в 00:08

fess2007

DD-DepartDate
mindays - просто алиас который означает max((DATEDIFF(DD,DD,@Todaydate))) из внутреннего подзапроса

URL

06.12.2010 в 00:21

Abigail

All of the love we left behind watching the flashbacks intertwine. I think our lives have just begun.

fess2007, а, это типа моего сокращения r или v у таблиц, так?
Ух ты, работает! Круто! У меня как раз есть похожий на этот запрос, так что теперь можно считать, что я сделала их оба)
Спасибо огромное, вы так мне помогли! :friend:

URL


Запомнить

MS SQL - проблема с запросом

ru_programming