[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 227
  • Last Modified:

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.
0
jc50967w
Asked:
jc50967w
  • 4
  • 3
1 Solution
 
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
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
 
LowfatspreadCommented:
sorry
set metric_count = x.seq
0
 
jc50967wAuthor Commented:
it works. Lowfatspread . Thank you very much
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now