A query that is over my head...

Question:
How do I return a list of items that matches one or more criteria that I pass in?

Background:
A user enters a sales lead (a company is looking for a place to have their event). That lead has a number of
criteria elements (start date, end date, city, region, maximum room rate, one or more amenities, etc. - more
details below) that should be used when trying to find Hotels that match that criteria. Obviously, some
criteria is more important than others (city, start and end date are more important than the maximum room
rate) - and it's unlikely that many (if any) of the Hotels will match *all* of the criteria entered by the
user. So, I'm looking to return a list of Hotels that match at least one of the criteria - if possible,
ordered by how many criteria elements match.

What makes this query particularly difficult, is that some of the criteria to match are stored in multiple
tables. For example, each Hotel has "Amenities" (Golf, Spa, etc.) - that are stored in a seperate table.
When a user enters a lead, they select which amenities they want to match. Also, a lead specifies a number
of rooms to block for each day between the Arrival and Departure date - these numbers can change from day
to day - but for this query - I think it's acceptible to get the largest number of rooms needed from any of
the days and compare that one number against the "MaxDailyRoomBlock" field of a Hotel (represented by the
"Property" table). Also, since a Hotel has different rates defined for each season, the query will have to
match the "MaxRate" against the rate of the correct season based on the Arrival and Departure dates. Also,
the rate can be within 20% of the stated "MaxRate".

Here are the following variables that will be passed into the query as criteria items:
RequestCity, RegionINDEID, ArrivalDate, DepartureDate, MaxRate, MaxTheaterSeating, MaxBanquetSeating,
MaxSchoolSeating, MaxBreakoutRooms, MaxRoomBlock

I know this is a huge post - and I sincerly appreciate any help you can provide.

DDL for Tables:
*In the DDL.txt attachment

Sample Data:
* In the data.txt attachment

Previous Attempts:
Unfortunately, I don't even know where to begin, so I haven't tried anything yet.

Expected Results:

PropertyID Name NumOfMatches
------ ------ ----
1 Marriot San Diego 5
2 Hilton San Diego 3
3 Hilton San Diego Downtown 2

Thanks in advance, again...Sounds like you need weighted values, not just a count of how many matches.
You are going to need to do this in a stored procedure, and it will likely require serveral steps depending upon the complexity of the schema and business requirements.
I strongly suggest you find a DBA proficient in SQL programming to help you with this, as it could end up being a big job for you.|||I can give some enlightment to ur problem.place the mandatory certeria in ur where clause,(eg:regionID,startdate,enddate) and put the other certeria in ur case statement as follows.
I have given weightage 1 for every certeria,u can change as per ur requirement(u can see at 'case statment')

NB:I didnt understand ur 20% of Maxrate.give me a example


-- details--
select l.LeadID,p.PropertyID,p.Name,
case when l.MaxDailyRoomBlock<=p.MaxDailyRoomBlock then 1
else 0 end as block,
case when l.MaxBreakoutRooms<=p.MaxBreakoutRooms then 1
else 0 end as breaks,
case when l.MaxTheaterSeating<=p.MaxTheaterStyleSeats then 1
else 0 end as Theater,
case when l.MaxBanquetSeating<=p.MaxBanquetStyleSeats then 1
else 0 end as Banquet,
case when l.MaxSchoolSeating<=p.MaxSchoolStyleSeats then 1
else 0 end as School,

(select count(*) from LeadAmenity la,
PropertyAmenity pa where l.LeadID=la.LeadID
and la.AmenityID=pa.AmenityID and p.PropertyID=pa.PropertyID) as AmenityCount
from
Property p,
Lead l





-- summary---
select LeadID,PropertyID,Name,(block+breaks+Theater+Banqu et+School+AmenityCount) as NumOfMatches
from
(
select l.LeadID,p.PropertyID,p.Name,
case when l.MaxDailyRoomBlock<=p.MaxDailyRoomBlock then 1
else 0 end as block,
case when l.MaxBreakoutRooms<=p.MaxBreakoutRooms then 1
else 0 end as breaks,
case when l.MaxTheaterSeating<=p.MaxTheaterStyleSeats then 1
else 0 end as Theater,
case when l.MaxBanquetSeating<=p.MaxBanquetStyleSeats then 1
else 0 end as Banquet,
case when l.MaxSchoolSeating<=p.MaxSchoolStyleSeats then 1
else 0 end as School,

(select count(*) from LeadAmenity la,
PropertyAmenity pa where l.LeadID=la.LeadID
and la.AmenityID=pa.AmenityID and p.PropertyID=pa.PropertyID) as AmenityCount
from
Property p,
Lead l
) as tm order by LeadID,PropertyID


come back if u have any doubts|||Thanks a ton for your reply, and sorry for being so slow to say so - I've been overwhelmed by the same project that prompted this question. It's helped me out a lot... thanks!

Chad