Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2005
    Posts
    2

    Unanswered: 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:
    Code:
    PropertyID		Name				NumOfMatches
    ----------------	-------------------		------------
    1			Marriot San Diego		5
    2			Hilton San Diego		3
    3			Hilton San Diego Downtown	2

    Thanks in advance, again...
    Attached Files Attached Files

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jun 2003
    Posts
    269

    Thumbs up

    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

    Code:
    --- 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

    Code:
    -- summary-------
    select LeadID,PropertyID,Name,(block+breaks+Theater+Banquet+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
    Last edited by mallier; 12-09-05 at 10:11.
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  4. #4
    Join Date
    Dec 2005
    Posts
    2
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •