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
Hall of Fame
Hi, this should get you somewhere near:
;with cte as (
, ca.value as ca_value
, sa.value as sa_value
from ClientAttribute ca
join SupplierAttribute sa
on ca.attributeId = sa.attributeId
where ca.clientId = 1
, cte2 as (
, 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
join Supplier s
on cte.supplierId = s.supplierId
where s.supplierId not in (
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
Viewing 2 posts - 1 through 1 (of 1 total)