finding the best match

  • Hi All

    I have project that basically requires me to “find the best match” solution.

    Any advice/guidance on what the best solution would be (in terms of performance) will be appreciated (CLR/SET Based /Cursor (arrrh) etc).

    Clients have a set of attributes and each attribute has a “value”, this value can be 0 (not essential), 1 (preferred) and 2 (mandatory). Each supplier has the same set of attributes, but their value is a simple true/false property.

    If ClientB has all attribute set to “2”, then only suppliers that have and value of “1” should be select, if more than one supplier is returned then a sort on supplier.rate would be required. With the data supplied it should return SupplierA and SupplierC.

    As a result of this, SupplierA and B have now been made excluded from the next search for ClientA. As you may see this client only has preferred attribute therefore suppliers with “1” and “0” can be returned… As this is preferable then ideally supplierattribute.value should be “1” if supplier match.

    I have attached the schema and some sample data to match the scenario above (I think ;-))

    Thanks in advance…. Schema and Data attached...

    I have also posted on AskSSC

  • Hi, this should get you somewhere near:

    ;with cte as (

    select ca.attributeId

    , ca.value as ca_value

    , sa.supplierId

    , sa.value as sa_value

    from ClientAttribute ca

    join SupplierAttribute sa

    on ca.attributeId = sa.attributeId

    where ca.clientId = 1


    , cte2 as (

    select s.supplierId

    , sum(case when ca_value = 2 and sa_value = 1 then 1 else 0 end) as req_count

    , sum(case when ca_value = 1 and sa_value = 1 then 1 else 0 end) as pref_count

    , s.rate

    from cte

    join Supplier s

    on cte.supplierId = s.supplierId

    where s.supplierId not in (

    select supplierId

    from cte

    where ca_value = 2

    and sa_value = 0


    group by s.supplierId, rate


    select * from cte2

    order by req_count desc, pref_count desc, rate desc

    Regards, Iain

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply