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

Сетка мертвая... Посему смотрел кино. На ДВД. Дывыс... совершила очередной выезд на велосипеде по сумасшедшим ул... День, когда не решена ни одна из задач, работающих на дол...

Что тут было во время и после игры с Португалией!!! Ето н... утром в воскресенье выхожу из метро и вижу, желтые лилейн... лучше бы никого не видела... ненавижу маршрутки. Всегда т...

Комментарии

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