Tallal writes "I'm tearing my hair out over this, I'm sure that I've overlooked something really obvious and if anyone can help I would be eternaly greatful. The DB is MSSQL7SP3 on NT4sp6a .The Database============2 tables involved, one is a Delegate table the other is a DelegateHotelBooking Table.A delegate can have any number of hotel bookings, and (here's the killer) can also share any number of hotel bookings with other delegates. To put it another way - a hotel booking is a room booked in a hotel for either one or two delegates in a particular time period.Requirement===========I need a SELECT statement that returns the following:[]all delegates who have no bookings[]delegates who have a booking/s but none fall into the period @STARTDATE, @ENDDATE[]delegates who are sharing a booking/s but none fall into the period @STARTDATE, @ENDDATETable Defs==========Create Table dbo.Delegate ( DelegateID int Not Null Identity (1, 1), Title varchar(10) Null, FirstName varchar(50) Not Null, LastName varchar(50) Not Null /* SNIP cols that are not relavent */ )GoAlter Table dbo.Delegate Add Constraint PK_Delegate Primary Key Nonclustered ( DelegateID )Go/*------------------------------------------------------------------*/Create Table dbo.DelegateHotelBooking ( BookingID int Not Null Identity (1, 1), DelegateID int Not Null, ShareDelID int Null, ChkIn datetime Not Null, ChkOut datetime Not Null /* SNIP cols that are not relavent */ )GoAlter Table dbo.DelegateHotelBooking Add Constraint PK_DelegateHotelBooking Primary Key Nonclustered ( BookingID )GoAlter Table dbo.DelegateHotelBooking Add Constraint FK_DelegateHotelBooking_Delegate Foreign Key ( DelegateID ) References dbo.Delegate ( DelegateID )/*----------------------------------------------------------------*/
QUERY=====My best attempt at getting the records so far works fine if a delegate is only sharing one other room or has one other bookling.If they have multiple booking the result set is incorrect tho Alter Procedure get_sharingDelegates( @Sdate as datetime, @Edate as datetime)As/********************************************//*ALL DELS SHARING BUT NOT DURING THE PERIOD*/SELECT Delegate.DelegateID, Delegate.Title+''+Delegate.FirstName+' '+Delegate.LastName as FullNameFROM Delegate INNER JOIN DelegateHotelBooking ON Delegate.DelegateID = DelegateHotelBooking.ShareDelIDWHEREDelegateHotelBooking.ChkIn not between convert(datetime,@Sdate,103)and convert(datetime,@Edate,103) andDelegateHotelBooking.ChkOut not betweenconvert(datetime,@Sdate,103) and convert(datetime,@Edate,103)UNION
/************************************************************//*ALL DELS WHO HAVE HOTEL BOOKINGS BUT NOT DURING THE PERIOD*/
SELECT Delegate.DelegateID, Delegate.Title+''+Delegate.FirstName+' '+Delegate.LastName as FullNameFROM Delegate INNER JOIN DelegateHotelBooking ON Delegate.DelegateID = DelegateHotelBooking.DelegateIDWHEREDelegateHotelBooking.ChkIn not between convert(datetime,@Sdate,103) and convert(datetime,@Edate,103)andDelegateHotelBooking.ChkOut not between convert(datetime,@Sdate,103) and convert(datetime,@Edate,103)UNION
/****************************************//*ALL DELS WHO DONT HAVE HOTEL BOOKINGS */
SELECT Delegate.DelegateID, Delegat
problems selecting the correct data subset 
Posted - 2001-09-27 : 09:16:39