UDF for count products for each order

experts,

I am writing a UDF to count how many products for each order, and assign squential number to each of products in a table. Orders is named Policy, products is titled Metric.
Table Name: F_Metric_Risk
PolicyID (int)    MetricID(bigint)  Metric_Count(tinyint)
P0001             M0001                
P0001             M0002
....                   ....
P0001             M0038
P0002             M0001
....                 ....
P0002            M0021
P0003            M0001
...                  ....
I want to count MetricID for each PolicyID, and assign 1, 2, 3,...whatever number into Metric_Count
Here is part of my UDF, I got lost on logic in somewhere:

CREATE FUNCTION UDF_COUNT_PGS (@PolicyID int)  
RETURNS  int
as
BEGIN
 declare @count int,  @MetricID tinyint
       -- // get the total number of pgs for a contract
      set @count = select Count(MetricID) as Metric_Count
                  from F_Metric_Risk
                  group by PolicyID, MetricID
                  having PolicyID = @PolicyID and Penalty_Amt is null --// ignore Penalty_Amt column for now.

       --  // return an interger for each pg on sequence
      set @MetricID = 1
      while @MetricID < = @count
            return @MetricID
                  @MetricID = @MetricID + 1
      End --// end while loop            
      
END

Please help me, thanks.
jc50967wAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony PerkinsCommented:
Do you not have a Primary Key on F_Metric_Risk?  Otherwise it is going to be a real dog (that is slow)
0
jc50967wAuthor Commented:
Yes, another field, a surrogate key. I didn't include here
0
jc50967wAuthor Commented:
Shoulde I include the pk column, Product_sk in the UDF?
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

LowfatspreadCommented:
can you explain your requirment in more detail as i'm unconvinced that a udf is appropriate in this case...

i'd be advocating a straight forward table self join and a group by count(*) to obtain the sequence ...
as you appear to need to call/invoke the function for each row in this case an repeatedly cause the
current metrics sequence to be determined rather than having "all" metrics for the policy sequenced
in one go...

hth
 
0
jc50967wAuthor Commented:
I know it's possible to use a update query to assgin value into the empty column Metric_Count.

currently, my table look like
PolicyID (int)    MetricID(bigint)  Metric_Count(tinyint)
P0001             M0001                
P0001             M0002
....                   ....
P0001             M0038
P0002             M0001
....                 ....
P0002            M0021
P0003            M0001
...                  ....

then I want to fill in the column Metric_Count
PolicyID (int)    MetricID(bigint)  Metric_Count(tinyint)
P0001             M0001                 1
P0001             M0002                 2
....                   ....
P0001             M0038                 38
P0002             M0001                 1
....                 ....
P0002            M0021                  21
P0003            M0001                    1
...                  ....

BMW, values in PolicyID and MetricID is not like what I illustrate here. They are both surrogate key also, I showed the data type beside the column name.
0
LowfatspreadCommented:
update metric_risk
 set metric_count = a.seq
 from metric_risk as a
 inner join (select b.policyid,b.metricid,count(*) as seq
                   from metic_risk as b
                  inner join metric_risk as c
                      on b.policyid=c.policyid
                    and b.metricid<=c.metricid
                  group by b.policyid,b.metricid
                 ) as x
  on a.metricid=x.metricid
 and a.policyid=x.policyid
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LowfatspreadCommented:
sorry
set metric_count = x.seq
0
jc50967wAuthor Commented:
it works. Lowfatspread . Thank you very much
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.