Decision making procedure - SQL Server Forums

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)

Decision making procedure

Author

Topic

netjam
Starting Member

37 Posts

Posted - 2008-05-22 : 03:08:41

I have a decision table, which is a list of id’s
exmpl. DecisionID, Did1,Did2,Did3, …Did8

Every record has a unique combination of Did.
But not necessary for every combination. Let say for 8 id’s it would be 30 combination and one default (if required combination is not found) but not 64.
User passes values for every Did.

I need something like a matrix style logic to go through every possible combination until match found or exit to default.

Any Ideas

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-22 : 03:25:21

What are the possible values in DID1-DID8?

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-22 : 03:52:25

Please post some sample data to illustrate the combination

netjam
Starting Member

37 Posts

Posted - 2008-05-22 : 04:32:59

range would be max from 1 to 999
Did1 = 3
Did2 = 12
Did3 = 3
Did4 = 56
...
so any number that range
but not for all let say Did1 has range 1-20
Did2 has range from 1-500 and so on
Each Did represents different parts of decision making process
Did1 could be CountryID.
Did2 let say SportID and so on

So input values example: 2,4,55,23,12,1,156,4
I need to get a record with maximum matches
Let say the max match would be:
0,4,55,23,0,0,0,4
The default record would have all id's with value 0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-22 : 06:10:14

quote:
Originally posted by netjam

range would be max from 1 to 999
Did1 = 3
Did2 = 12
Did3 = 3
Did4 = 56
...
so any number that range
but not for all let say Did1 has range 1-20
Did2 has range from 1-500 and so on
Each Did represents different parts of decision making process
Did1 could be CountryID.
Did2 let say SportID and so on

So input values example: 2,4,55,23,12,1,156,4
I need to get a record with maximum matches
Let say the max match would be:
0,4,55,23,0,0,0,4
The default record would have all id's with value 0

SELECT TOP 1 *
FROM
(
SELECT *,
      CASE WHEN Did1 = @Did1 THEN 1 ELSE 0 END +
CASE WHEN Did2 = @Did2 THEN 1 ELSE 0 END +
CASE WHEN Did3 = @Did3 THEN 1 ELSE 0 END +
CASE WHEN Did4 = @Did4 THEN 1 ELSE 0 END +
CASE WHEN Did5 = @Did5 THEN 1 ELSE 0 END +
CASE WHEN Did6 = @Did6 THEN 1 ELSE 0 END +
CASE WHEN Did7 = @Did7 THEN 1 ELSE 0 END +
CASE WHEN Did8 = @Did8 THEN 1 ELSE 0 END AS MatchValue
FROM YourTable
WHERE Did1 = @Did1 
OR Did2 = @Did2 
OR Did3 = @Did3 
OR Did4 = @Did4 
OR Did5 = @Did5 
OR Did6 = @Did6 
OR Did7 = @Did7
OR Did8 = @Did8
OR Did1+Did2+Did3+Did4+Did5+Did6+Did7+Did8=0)t  
ORDER BY MatchValue DESC

netjam
Starting Member

37 Posts

Posted - 2008-05-22 : 06:16:59

Thanks.
I'll give it a try in a morning.

netjam
Starting Member

37 Posts

Posted - 2008-05-22 : 19:45:53

Does the job and very compact

Excellent

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources