October 19, 2010 at 4:13 am
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
http://ask.sqlservercentral.com/questions/21658/guidance-required-with-matching
October 19, 2010 at 6:07 am
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