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)
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)
declare @count int, @MetricID tinyint
-- // get the total number of pgs for a contract
set @count = select Count(MetricID) as Metric_Count
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
@MetricID = @MetricID + 1
End --// end while loop
Please help me, thanks.