?
Solved

match making (complex)

Posted on 2005-04-23
5
Medium Priority
?
251 Views
Last Modified: 2010-03-19
Hi,

How can we find set of records base on the given set of scores per category?

Please kindly help me create a store procedure that will return the Table_score.scoreID if found and return 0 if not.

Thanks.

i.e.

User gave Peter score's:
singing = 89                 <-------------------set of scores given by user
dancing = 78
eating = 90

table_category                                         <--containing categories criteria
MainCatID    SubCatID    SubDescription  
     1                  1              singing
     1                  2              dancing
     2                  1                eating      

Table_score
scoreID   MainCatID         SubCatID    score
1                1                       1             89   <---this is not a match has 4 categories (sing, dance, eat, other one unknown)
1                1                       2             78
1                2                       1             90
1                1                       3             10

2                1                       1             89                    <-----------we found a match singing   -l
2                1                       2             78                    <-----------we found a match dancing  -l--perfect set match 3 cat
2                2                       1             90                    <-----------we found a match eating     -l

3                1                       1             89 <---this is not a match since it has 2 categories (singing, dancing)
3                1                       2             78                    



return scoreID -which is 2
else
return 0

*one condition scoreID should be the same for the set of scores.
Table_score
scoreID   MainCatID         SubCatID    score
2                1                       1             89                    <----------2
2                1                       2             78                    <----------2
2                2                       1             90                    <----------2



Thanks.
0
Comment
Question by:EdwardPeter
  • 2
  • 2
5 Comments
 
LVL 5

Expert Comment

by:xnmb
ID: 13849314
would the input parameters for the SP be a set of IDs of categories? and find the scoreid that matches these set of IDs?
0
 
LVL 17

Accepted Solution

by:
BillAn1 earned 2000 total points
ID: 13849431
it will depend on how you want to call the procedure / function and how much flexibility you want to have etc. The following function is an example of what you can do. This function will take up to 4 sets of scores, and will return the related score value. You can modifiy to accommodate the maxsimum number of scores you need to pass. An alternative approach if you do not know hte maximum number, or if it is too large to realistically pass as seperate parameters, is to create the equivalent to @sc as a normal table, and populate it before you call GetScoreID, or you could pass it an XML string, or even a simple concatenated string which you could parse. All depends on your requirements. In any case, the SQL will be basically the same, it is just how you get your input data into the @sc table to begin with.
I propose you use a function rather than a procedure, because stored procs are not really intended to return a value (although they can return a value and/or have an output parameter, the return value is normally used to return a success / failure status, not a parametric value) but you could easily implement as a procedure.
 

create function GetScoreID (
      @desc1 varchar(32), @score1 integer,
      @desc2 varchar(32), @score2 integer,
      @desc3 varchar(32), @score3 integer,
      @desc4 varchar(32), @score4 integer
)

returns integer
as
begin
      declare @sc table (descr varchar(32), score integer)
      if @desc1 is not null insert into @sc values (@desc1, @score1)
      if @desc2 is not null insert into @sc values (@desc2, @score2)
      if @desc3 is not null insert into @sc values (@desc3, @score3)
      if @desc4 is not null insert into @sc values (@desc4, @score4)


      declare @scoreID integer

      select @scoreID = min(scoreID)
      from @sc sc
      join table_category tc on sc.descr = tc.SubDescription
      join Table_score ts on ts.MainCatID = tc.maincatid and ts.subcatid = tc.subcatid and ts.score = sc.score
      group by scoreID
      having count(*) = (select count(*) from table_score ts2 where ts2.scoreID = ts.scoreID)
      and count(*) = (select count(*) from @sc)

      set @scoreID = isnull(@scoreID,0)      
      
      return @scoreID
end


with your sample data, you could call this as :
   select dbo.GetScoreID ('singing',89,'dancing',78,'eating',90, NULL, NULL)
will return 2 (all 3 scores match OK)

   select dbo.GetScoreID ('singing',89,'dancing',78,NULL,NULL, NULL, NULL)
will return 3 (both scores match OK)

   select dbo.GetScoreID ('singing',89,'dancing',78,'eating',91, NULL, NULL)
will return 0 (doesn't match exactly any of them)

0
 

Author Comment

by:EdwardPeter
ID: 13849518
BillAn1,

Amazing, it took my breath away!

Yes, that is correct flexibility is the key, specially the part where user will add/delete categories.

table_category                                      
MainCatID    SubCatID    SubDescription  
     1                  1              singing
     1                  2              dancing
     2                  1                eating      

When user add's one we will need to modify the Stored Function, is there anyway we can lookup the number of records in table_categories instead of manually editing the stored function everytime there's going to be a change in the table?

Thanks.
0
 
LVL 17

Expert Comment

by:BillAn1
ID: 13850095
actually, strictly speaking hte function does not care about the #records in the table_category table. ALl it cares about is the number of parameters passed in, so if you call the function with 3 parameters (i.e. 3 sets of paramters which are not NULL), then it will check that there are 3 matching records in the table_score table, if you call it with 4 it will match for 4 etc. If you know that the maximum # ever will be say 5, you could create the function with 5 sets of parameters, and then just call it with the required # of not-null parameters.
If you want more flexibility, you could call it with a single comma-delimited parameter, such as :

alter function GetScoreID (@parameters varchar(1000))
returns integer
as
begin

     declare @sc table (descr varchar(32), score integer)
     declare @descr varchar(32)
     declare @score integer

     declare @scoreID integer

     while @parameters <> ''
     begin
        set @descr = left(@parameters, charindex(',',@parameters)-1)
        set @parameters = right(@parameters, len(@parameters) -  charindex(',',@parameters))
      if charindex(',',@parameters) > 0
      begin
              set @score = cast(left(@parameters, charindex(',',@parameters)-1) as integer)
              set @parameters = right(@parameters, len(@parameters) -  charindex(',',@parameters))
      end
      else
      begin
            set @score = cast(@parameters as integer)
            set @parameters = ''
      end
      insert into @sc values (@descr, @score)
     end    

     select @scoreID = scoreID
      from @sc sc
     join table_category tc on sc.descr = tc.SubDescription
     join Table_score ts on ts.MainCatID = tc.maincatid and ts.subcatid = tc.subcatid and ts.score = sc.score
     group by scoreID
     having count(*) = (select count(*) from table_score ts2 where ts2.scoreID = ts.scoreID)
     and count(*) = (select count(*) from @sc)

     set @scoreID = isnull(@scoreID,0)    
     
     return @scoreID
end


then you can call this as :
select dbo.GetScoreID2('singing,89,dancing,78,eating,90')
you can just pass as many parameters as you like in this case.
a couple of caveats :
1. you can't have a description with a comma in it, as commas are now used to delimit the list  (e.g. a description of "Hop,skip and jump" would cause the function to fall over)
2. You might want to put some error handling to make sure that the parameter passed in is of the correc tofrmat - e.g. that it contains an even # of parameters, and that each 2nd one is actually a number etc. otherwise the function might crash if it is called incorrectly.
0
 

Author Comment

by:EdwardPeter
ID: 13853398
BillAn1,

Your simply the best. You rock my world.

Thanks.
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question