CREATE TABLE [dbo].[Meetingroom_capacity] (
	[foreignKey] [int] NOT NULL ,
	[capacityBoardroom] [int] NULL ,
	[capacityClass] [int] NULL ,
	[capacityTheatre] [int] NULL ,
	[capacityUshape] [int] NULL 
)

declare @roomtype varchar(something),
        @capacity int

-- your first example under the 'eg:'
-- you would replace these with the input parms from your proc
select @roomtype = 'classroom', @capacity = 100

select
    r.roomname
from
    rooms r
    inner join Meetingroom_capacity c
        r.roomid = c.foreignkey
where
    (@roomtype = 'Classroom' and c.CapacityClass = @capacity) or
    (@roomtype = 'Boardroom' and c.CapacityBoard = @capacity) or
    (@roomtype = 'Theatre' and c.CapacityTheatre = @capacity) or
    (@roomtype = 'U-Shape' and c.CapacityUShape = @capacity)

CREATE TABLE [dbo].[Meeting_Rooms] (
	[primaryKey] [int] NOT NULL ,
	[foreignKey] [int] NULL ,
	[facilities] [ntext] NULL ,
	[floor] [nvarchar] (20) NULL ,
	[lightingText] [nvarchar] (20) NULL ,
	[lightingType] [nvarchar] (200) NULL ,
	[meetroomName] [nvarchar] (30) NULL ,
	[order] [int] NULL ,
	[suite] [nvarchar] (30) NULL 
)


CREATE TABLE [dbo].[Meetingroom_capacity] (
	[foreignKey] [int] NOT NULL ,
	[capacityBoardroom] [int] NULL ,
	[capacityClass] [int] NULL ,
	[capacityTheatre] [int] NULL ,
	[capacityUshape] [int] NULL 
)

-- usp_multiroom 'Boardroom,Boardroom,Classroom', '30,100,150'
create procedure usp_multiroom
    @roomlist varchar(1000),        --comma seperated list of desired rooms
    @delegateslist varchar(1000)    --comma seperated list of desired delegates
                                    --    (should match 1for1 with @roomlist)
as

create table #reservations (        --temp table to hold processed lists
    resid integer identity(1,1),
    room varchar(50),
    del smallint,
    mtgroom int
)


declare @separator_position_room int,
        @separator_position_del int,
        @array_value_room varchar(50),
        @array_value_del varchar(50),
        @resid int,
        @maxresid int


--*****************************************************************************
--Step 1: parse the parm lists
--        note: if you are on 2k, you should make this generic and put it in a
--              UDF . . .
--*****************************************************************************

select @roomlist = @roomlist + ','
select @delegateslist = @delegateslist + ','

while patindex('%,%', @roomlist) <> 0
begin
    select @separator_position_room = patindex('%,%', @roomlist)
    select @separator_position_del  = patindex('%,%', @delegateslist)
    if @separator_position_del = 0
        print 'Do some error handling'


    select @array_value_room = left(@roomlist, @separator_position_room-1)
    select @array_value_del = left(@delegateslist, @separator_position_del-1)

    insert #reservations (room, del)
        values(@array_value_room, convert(smallint, @array_value_del))

    select @roomlist = stuff(@roomlist, 1, @separator_position_room, '')
    select @delegateslist = stuff(@delegateslist, 1, @separator_position_del, '')

end

--*****************************************************************************
--Step 1: match up the desired reservations with a meeting room
--*****************************************************************************

select
    @resid = 1,
    @maxresid = max(resid)
from
    #reservations

while @resid <= @maxresid
begin

    update #reservations
    set    mtgroom = (
        select
            min(mr.primarykey)
        from
            Meeting_rooms mr
            inner join Meetingroom_capacity mc
                on mr.primarykey = mc.foreignkey
            inner join #reservations r
                on ((r.room = 'Classroom' and mc.CapacityClass = r.del) or
                    (r.room = 'Boardroom' and mc.CapacityBoardroom = r.del) or
                    (r.room = 'Theatre' and mc.CapacityTheatre = r.del) or
                    (r.room = 'U-Shape' and mc.CapacityUShape = r.del))
        where
            resid = @resid and
            not exists (
                select 1
                from
                    #reservations
                where
                    mtgroom = mr.primarykey) )
    where
        resid = @resid

    set @resid = @resid + 1    
end

select *
from #reservations

drop table #reservations

go

quote:

---

...but your solution doesn't tackle the somewhat complex problem of matching all rooms at once, which is the main issue I have....
Thanks,
Nick...

---

quote:

---

quote:

---

...but your solution doesn't tackle the somewhat complex problem of matching all rooms at once, which is the main issue I have....
Thanks,
Nick...

---

OK, I am completely lost then. You are passing in two comma-sep lists into this proc and it gives a matching room for each roomtype/capacity combo . . . all at once. It won't pick the same room for two roomtype/capacity pairs ... You can mark up the proc to actually do the reservation, I didn't have your reservation schema.

<throwing-in-towel>Best of luck to you . . .</throwing-in-towel>

Jay

---

SELECT Meeting_Rooms.foreignKey AS VenueID,
Meeting_Rooms.primaryKey as RoomID,
 capacityBoardroom, capacityClass
FROM Meeting_Rooms 
INNER JOIN Meetingroom_capacity ON Meeting_Rooms.primaryKey = Meetingroom_capacity.foreignKey
WHERE     (NOT (capacityBoardroom = 0)) AND (NOT (capacityClass = 0))
ORDER BY Meeting_Rooms.foreignKey, capacityBoardroom, capacityClass

VenueID     RoomID      capacityBoardroom capacityClass 
----------- ----------- ----------------- ------------- 
22          2204        16                18
22          2207        20                20
22          2201        20                25
22          2202        20                25
22          2203        20                35
23          2301        52                60
24          2403        36                36
24          2402        80                130
24          2401        120               150
27          2701        40                60
28          2807        30                45
28          2806        50                50
28          2805        60                60
29          2904        24                12
29          2905        24                12
29          2906        24                12
29          2903        30                40
29          2901        70                120
29          2902        70                120
30          3005        14                10
30          3007        14                14
30          3006        20                50
30          3003        30                50
30          3004        30                50
30          3002        50                100

CREATE TABLE [dbo].[Venue] (
	[primaryKey] [int] NOT NULL,
	[addressLine1] [nvarchar] (75)  NULL,
	[addressLine2] [nvarchar] (75)  NULL,
	[associationNames] [ntext]  NULL,
	[bedroomFacilities] [ntext]  NULL,
	[conferenceEquipment] [ntext]  NULL,
	[country] [nvarchar] (30)  NULL,
	[county] [nvarchar] (28)  NULL,
	[displayType] [nvarchar] (4)  NULL,
	[email] [ntext]  NULL,
	[equipDescription] [ntext]  NULL,
	[fax] [nvarchar] (70)  NULL,
	[groupIDs] [nvarchar] (3)  NULL,
	[placesofInterest] [ntext]  NULL,
	[postcode] [nvarchar] (8)  NULL,
	[region] [nvarchar] (28)  NULL,
	[tel] [nvarchar] (74)  NULL,
	[town] [nvarchar] (30)  NULL,
	[URL] [ntext]  NULL,
	[venueFacilities] [nvarchar] (75)  NULL,
	[venueName] [nvarchar] (75)  NOT NULL,
	[venueStyle] [ntext]  NULL 
)

quote:

---

[Alright, I am taking the towel back . . . I see the problem, gimme a minute . . .

Jay

---

-- usp_multiroom 'Boardroom,Boardroom,Classroom,U-Shape', '30,100,150,75'
alter procedure usp_multiroom
    @roomlist varchar(1000),        --comma seperated list of desired rooms
    @delegateslist varchar(1000)    --comma seperated list of desired delegates
                                    --    (should match 1for1 with @roomlist)
as

create table #reservations (        --temp table to hold processed lists
    resid integer identity(1,1),
    room varchar(50),
    del smallint,
    mtgroom int
)

create table #venues (              --temp table to hold possible venues
    venid integer identity(1,1),
    venueid integer
)

declare @separator_position_room int,
        @separator_position_del int,
        @array_value_room varchar(50),
        @array_value_del varchar(50),
        @resid int,
        @maxresid int,
        @venid int,
        @maxvenid int,
        @venueid int

--*****************************************************************************
--Step 1: parse the parm lists
--        note: if you are on 2k, you should make this generic and put it in a
--              UDF . . .
--*****************************************************************************

select @roomlist = @roomlist + ','
select @delegateslist = @delegateslist + ','

while patindex('%,%', @roomlist) <> 0
begin
    select @separator_position_room = patindex('%,%', @roomlist)
    select @separator_position_del  = patindex('%,%', @delegateslist)
    if @separator_position_del = 0
        print 'Do some error handling'


    select @array_value_room = left(@roomlist, @separator_position_room-1)
    select @array_value_del = left(@delegateslist, @separator_position_del-1)

    insert #reservations (room, del)
        values(@array_value_room, convert(smallint, @array_value_del))

    select @roomlist = stuff(@roomlist, 1, @separator_position_room, '')
    select @delegateslist = stuff(@delegateslist, 1, @separator_position_del, '')

end

--*****************************************************************************
--Step 2: get a list of possible venue id that will full-fill all the room requests
--        a possible venue is one where at least one reseration requirement is met
--*****************************************************************************

select
    mr.foreignkey as veneuid
into #venuestemp
from
    Meeting_rooms mr
    inner join Meetingroom_capacity mc
        on mr.primarykey = mc.foreignkey
    inner join #reservations r
        on ((r.room = 'Classroom' and mc.CapacityClass >= r.del) or
            (r.room = 'Boardroom' and mc.CapacityBoardroom >= r.del) or
            (r.room = 'Theatre' and mc.CapacityTheatre >= r.del) or
            (r.room = 'U-Shape' and mc.CapacityUShape >= r.del))
group by mr.foreignkey
order by count(*) desc

--This little dance is done to get the venues with the most rooms at the top to make
--the search faster
insert #venues
select * from #venuestemp

--*****************************************************************************
--Step 3: match up the desired reservations with a meeting room
--*****************************************************************************

--set up the control variables
select
    @resid = 1,
    @maxresid = max(resid)
from
    #reservations

select
    @venid = 1,
    @maxvenid = max(venid)
from
    #venues

--Loop through all the possible venues looking for one that has all the rooms we need
while @venid <= @maxvenid
begin
    --Loop through all the reservation requirements and match to a room in this venue
    while @resid <= @maxresid
    begin
        --Put the qualifying room into the #reservations table
        update #reservations
        set    mtgroom = (
            select
                min(mr.primarykey)
            from
                Meeting_rooms mr
                inner join Meetingroom_capacity mc
                    on mr.primarykey = mc.foreignkey
                inner join #reservations r
                    on ((r.room = 'Classroom' and mc.CapacityClass >= r.del) or
                        (r.room = 'Boardroom' and mc.CapacityBoardroom >= r.del) or
                        (r.room = 'Theatre' and mc.CapacityTheatre >= r.del) or
                        (r.room = 'U-Shape' and mc.CapacityUShape >= r.del))
                inner join #venues v
                    on v.venueid = mr.foreignkey
            where
                resid = @resid and
                venid = @venid and
                not exists (
                    select 1
                    from
                        #reservations
                    where
                        mtgroom = mr.primarykey) )
        where
            resid = @resid
    
        set @resid = @resid + 1    
    end

    --Lets see if we matched all requirements for this venue
    if exists (select 1
               from
                    #reservations
               where
                    mtgroom is null)
    begin
        -- nope, start over with a new venue
        update #reservations
        set mtgroom = null

        select @resid = 1, @venid = @venid +1
    end
    else
    begin
        -- hey, we found a match . . . giddy-up
        select @venueid = venueid
        from #venues
        where @venid = venid

        break
    end
end

select @venueid

drop table #reservations
drop table #venues
go

quote:

---

If Jay comes up with the solution, I won't spend the hour or so I think I will need and I am sure you won't want to waste your time loading exporting some tables.

Jay, does your solution use cursors or did you figure out a setbased solution?

Jeremy

---

Cursors . . . CURSE YOU!! But it isn't set based . . .


Jay