finding the best match for a set of params

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

finding the best match for a set of params

Author

Topic

talnimzo
Starting Member

2 Posts

Posted - 2006-05-10 : 14:09:20

Here is my situation:
OS=Windows 2003
MSSQL = 2005

My application deals with financial transactions. When a loan is created, a set of documents and tasks are associated with it. The users of the application can set up new groups of documents and tasks that are to be applied to specific loan types.

The criteria for the loan type consists of three id fields:
LenderId
PropertyTypeId
ProductTypeId

The table that contains the data is as follows:

CREATE TABLE TaskGroups
(
TaskGroupId int PRIMARY KEY IDENTITY,
LenderId int not Null default 0,
PropertyTypeId int not Null default 0,
ProductTypeId int not NULL default 0
)

I need the ability to get exactly one record from this table when the loan is created. However, not all LenderIds, PropertyTypeIds, and ProductIds are in the table. Instead zeros are used as wildcards. The reason for this is that there are thousands of each type and I don’t want the users to have to set up every combination.

The idea is that there is a default record (all wildcards) that can be superseded by more specific records where appropriate. This would allow users to say

Most specific:
“Use this TaskGroup for all loans where LenderId = @LenderId AND PropertyTypeId = @PropertyTypeId AND ProductTypeId = @ProductTypeId”

Less specific:
“Use this TaskGroup for all loans where LenderId = @LenderId AND PropertyTypeId = @PropertyTypeId AND ProductTypeId <> @ProductTypeId”

Even Less specific:
“Use this TaskGroup for all loans where LenderId = @LenderId AND PropertyTypeId <> @PropertyTypeId AND ProductTypeId <> @ProductTypeId”

Default:
“Use this TaskGroup for all loans where LenderId <> @LenderId AND PropertyTypeId <> @PropertyTypeId AND ProductTypeId <> @ProductTypeId”

I’m trying to find a way to make this work such that my query takes 3 parameters and returns the best result:

DECLARE @LenderId int, @PropertyTypeId int, @ProductTypeId int

The query would scan the table looking for the best match taking into account the zero as a wildcard.

Sample data for the table:

Insert TaskGroups VALUES (0,0,0)
Insert TaskGroups VALUES (1,1,1)
Insert TaskGroups VALUES (1,1,0)
Insert TaskGroups VALUES (1,0,0)
Insert TaskGroups VALUES (1,0,1)
Insert TaskGroups VALUES (0,1,0)
Insert TaskGroups VALUES (0,1,1)
Insert TaskGroups VALUES (0,0,1)

Results in:

TaskGroupId LenderId PropertyTypeId ProductTypeId
----------- ----------- -------------- -------------
1 0 0 0
2 1 1 1
3 1 1 0
4 1 0 0
5 1 0 1
6 0 1 0
7 0 1 1
8 0 0 1

My desired results based on the parameters are:

When there is a perfect match
Set @LenderId = 0
Set @PropertyTypeId = 0
Set @ProductTypeId = 0

Return TaskGroupId 1

Set @LenderId = 0
Set @PropertyTypeId = 1
Set @ProductTypeId = 0

Return TaskGroupId 6

Less specific match
Set @LenderId = 2
Set @PropertyTypeId = 0
Set @ProductTypeId = 0

Return TaskGroupId 1 (but not 4 because LenderId = 2 doesn’t exist in the table, so return the record with LenderId = wildcard (0) instead)

Set @LenderId = 1
Set @PropertyTypeId = 0
Set @ProductTypeId = 3

Return TaskGroupId 4 (ProductTypeId = 3 doesn’t exist in the table, so return the record with ProductTypeId = the wildcard (0) instead)

When there is no match
Set @LenderId = 2
Set @PropertyTypeId = 2
Set @ProductTypeId = 2

Return TaskGroupId 1

Set @LenderId = 200
Set @PropertyTypeId = 200
Set @ProductTypeId = 200

Return TaskGroupId 1

Set @LenderId = 20000
Set @PropertyTypeId = 20000
Set @ProductTypeId = 200000

Return TaskGroupId 1

Set @LenderId = 2000000
Set @PropertyTypeId = 200000
Set @ProductTypeId = 200000

Return TaskGroupId 1

I’m not married to my design, but I want the intended functionality: The users set up a default TaskGroup (all wildcards) then only have to setup lender, property, product specific TaskGroups as needed.

Any ideas?

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-11 : 06:19:30

Hi talnimzo,

No-one's responded to this yet, so I'll have a stab.

Here's one possibile technique...

--data
declare @TaskGroups table (TaskGroupId int PRIMARY KEY IDENTITY, LenderId int not Null default 0,
    PropertyTypeId int not Null default 0, ProductTypeId int not NULL default 0)
Insert @TaskGroups VALUES (0,0,0)
Insert @TaskGroups VALUES (1,1,1)
Insert @TaskGroups VALUES (1,1,0)
Insert @TaskGroups VALUES (1,0,0)
Insert @TaskGroups VALUES (1,0,1)
Insert @TaskGroups VALUES (0,1,0)
Insert @TaskGroups VALUES (0,1,1)
Insert @TaskGroups VALUES (0,0,1)

declare @Test table (Id int identity(1, 1), LenderId int, PropertyTypeId int, ProductTypeId int)
insert @Test
          select 0, 0, 0 --1
union all select 0, 1, 0 --6
union all select 2, 0, 0 --1
union all select 1, 0, 3 --4
union all select 2, 2, 2 --1
union all select 200, 200, 200 --1
union all select 20000, 20000, 200000 --1
union all select 2000000, 200000, 200000 --1

--calculation

--get all matches and their ratings
declare @Match table (TestId int, LenderId int, PropertyTypeId int, ProductTypeId int, TaskGroupId int, MatchRating tinyint)
insert @Match
    select a.Id, a.LenderId, a.PropertyTypeId, a.ProductTypeId, b.TaskGroupId,
        case when a.LenderId = b.LenderId then 1 else 0 end +
        case when a.PropertyTypeId = b.PropertyTypeId then 1 else 0 end +
        case when a.ProductTypeId = b.ProductTypeId then 1 else 0 end as MatchRating
    from @Test a left outer join @TaskGroups b on
        (a.LenderId = b.LenderId or b.LenderId = 0) and
        (a.PropertyTypeId = b.PropertyTypeId or b.PropertyTypeId = 0) and
        (a.ProductTypeId = b.ProductTypeId or b.ProductTypeId = 0)

--select the matches with the highest ratings
select * from @Match a
where MatchRating = (select max(MatchRating) from @Match where TestId = a.TestId)
order by TestId

BTW - you don't play chess by any chance do you (judging by your name)?

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

talnimzo
Starting Member

2 Posts

Posted - 2006-05-11 : 11:39:35

Ryan,

Thanks for the response. I've yet to try it out, but wanted to answer your question.

Yes, I play chess, but nowhere near the level of Mikhail Tal or Aron Nimzowitsch.

I tend to hover around 1300 USCF.

My chess-study time has been seriously limited by software requirements such as those described in this post!

Do you play? I go by PocketChange on ICC.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-11 : 12:01:22

Yeah, I play

I'm around 2200 USCF (I'm British and have a rating of BCF 186, so that's just a rough conversion). I spent far too much time studying as a kid!

I'm "bigdave" on ICC.

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources