SQL Scripting – udf_GetFilteredScore Function

SQL Scripted function: udf_GetFilteredScore

Returns a summarized value of a score. This avoids the need to get a “stat” from a score, but also provides additional filtering capabilities. The returned value can be used for comparison (i.e. select a score variable, return the average for everyone in the specified role, and carrying a specified descriptive).

 

Name: udf_GetFilteredScore

Arguments:

Name	Description
@personId	subject of the score (required); Supplied by the scripting engine, so use PersonId
@summaryFunction	values of ‘sum’, ‘avg’, ‘count’, ‘min’, ‘max’, ‘stdev’ (required)
@echelon	values of ‘instance’ or ‘module’ (required)
@memberOf	0 or 1; determines if the person has be in the role with the supplied descriptives. 0 =  not required; 1 = required
@role	role filter (required)
@scoreVariable	score name to evaluate (required); This DOES NOT have to be one of the indicators referenced in the score.
@descriptives	descriptive filter (optional; formatted like name1=value1|name2=value2); Use NULL if not used

 

Example usage:

dbo.udf_GetFilteredScore(PersonId, ‘avg’, ‘module’, 1, ‘candidate’, ‘Blocking’, ‘Year=2018|Rank=Major’)

 

The above example will give the average [Blocking] score from all candidates from within the module that have descriptive values of Year = 2018 AND Rank = Major. It will return NULL for any person not in the candidate role with those same descriptives. Using @memberOf = 0 will provide the output to everyone.

 

If you wanted to get all Ranks that are “Major” OR “Minor” then you could use a semi-colon as such:

dbo.udf_GetFilteredScore(PersonId, ‘avg’, ‘module’, 1, ‘candidate’, ‘Blocking’, ‘Year=2018|Rank=Major;Minor’)

 

You can take the output and use it in calculations and comparisons with the other indicators referenced in the score.

dbo.udf_GetFilteredScore(PersonId, ‘avg’, ‘instance’, 0, ‘candidate’, ‘Blocking’, ‘Year=2018|Rank=Major’) – [Average Blocking]

 

The above example takes the output from the function and  compares it to another score which is an indicator.

= = = = =

Version(s): GideonSoft 2022 Release 1 and later