We help IT Professionals succeed at work.

UDF for count products for each order

jc50967w
jc50967w asked
on
Medium Priority
242 Views
Last Modified: 2008-03-10
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.
Comment
Watch Question

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)

Author

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

Author

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
 

Author

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.

Ask the Experts
CERTIFIED EXPERT
Top Expert 2011

Commented:
sorry
set metric_count = x.seq

Author

Commented:
it works. Lowfatspread . Thank you very much
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

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

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.