We help IT Professionals succeed at work.

# UDF for count products for each order

on
Medium Priority
242 Views
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

Comment
Watch Question

## View Solution Only

CERTIFIED EXPERT
Top Expert 2012

Commented:
Do you not have a Primary Key on F_Metric_Risk?  Otherwise it is going to be a real dog (that is slow)

Commented:
Yes, another field, a surrogate key. I didn't include here

Commented:
Shoulde I include the pk column, Product_sk in the UDF?
CERTIFIED EXPERT
Top Expert 2011

Commented:
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

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.
CERTIFIED EXPERT
Top Expert 2011
Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

CERTIFIED EXPERT
Top Expert 2011

Commented:
sorry
set metric_count = x.seq

Commented:
it works. Lowfatspread . Thank you very much
##### Thanks for using Experts Exchange.

• View three pieces of content (articles, solutions, posts, and videos)
• Ask the experts questions (counted toward content limit)
• Customize your dashboard and profile