Login     Register

        Contact Us     Search

XLeratorDB/statistics Documentation

SQL Server Intra-class Coefficient function


ICC_TV

Updated: 24 May 2013


Use the table-valued function ICC_TV to calculate the intra-class coefficient. The ICC is used to assess the consistency of measurements made by multiple observers measuring the same quantity.
Syntax
SELECT * FROM [wctStatistics].[wct].[ICC_TV](
  <@InputData_RangeQuery, nvarchar(max),>
 ,<@TypeTest, nvarchar(4000),>
 ,<@Alpha, float,>
 ,<@r0, float,>)
Arguments
@InputData_RangeQuery
a T-SQL statement, as a string, that specifies the subject, rater, and rating values.
@TypeTest
ICC_TV support six different types of ICC:
·         one-way, consistency, single measure — ICC(1,1), '1-1'
·         one-way, consistency, average measure —ICC(1,k), '1-k'
·         two-way, agreement, single measure — ICC(2,1), '2-1'
·         two-way, agreement, average measure — ICC(2,k), '2-k'
·         two-way, consistency, single measure — ICC(3,1), '3-1'
·         two-way, consistency, average measure — ICC(3,k), '3-k'
@Alpha
the value to be used in the calculation of FL and FU. Must be of type float or of a type that implicitly converts to float.
@r0
the specification of the null hypothesis r = r0. r0 is used in the calculation of the F-value which is subsequently used in the calculation of the p-value. Must be of type float or of a type that implicitly converts to float.
Return Type
RETURNS TABLE (
      [r] [float] NULL,
      [F] [float] NULL,
      [df1] [float] NULL,
      [df2] [float] NULL,
      [p] [float] NULL,
      [FL] [float] NULL,
      [FU] [float] NULL,
      [LB] [float] NULL,
      [UB] [float] NULL
)

Column
Column Description
r
the test statistic
F
the F value
df1
the degrees of freedom 1 for the F distribution
df2
the degrees of freedom 2 for the F distribution
p
the p-value (calculated from the F distribution)
FL
the lower confidence level, for the specified alpha, of the F distribution
FU
the upper confidence level, for the specified alpha
LB
the p-value associated with FL
UB
the p-value associated with FU
Remarks
·         The function is insensitive to order; it does not matter what order the ratings are passed in.
·         Subjects, raters, ratings are automatically combined by the function.
Examples
DECLARE @case_icc as varchar(3)
SET @case_icc = '1-k'
 
SELECT n.s
,x.rater
,x.rating
INTO #i
FROM (
      SELECT 1,3,3,2 UNION ALL
      SELECT 2,3,6,1 UNION ALL
      SELECT 3,3,4,4 UNION ALL
      SELECT 4,4,6,4 UNION ALL
      SELECT 5,5,2,3 UNION ALL
      SELECT 6,5,4,2 UNION ALL
      SELECT 7,2,2,1 UNION ALL
      SELECT 8,3,4,6 UNION ALL
      SELECT 9,5,3,1 UNION ALL
      SELECT 10,2,3,1 UNION ALL
      SELECT 11,2,2,1 UNION ALL
      SELECT 12,6,3,2 UNION ALL
      SELECT 13,1,3,3 UNION ALL
      SELECT 14,5,3,3 UNION ALL
      SELECT 15,2,2,1 UNION ALL
      SELECT 16,2,2,1 UNION ALL
      SELECT 17,1,1,3 UNION ALL
      SELECT 18,2,3,3 UNION ALL
      SELECT 19,4,3,2 UNION ALL
      SELECT 20,3,4,2
      )n(s,r1,r2,r3)
CROSS APPLY(
      SELECT 'r1', r1 UNION ALL
      SELECT 'r2', r2 UNION ALL
      SELECT 'r3', r3
      )x(rater,rating)
     
SELECT p.*
FROM wct.ICC_TV('SELECT s,rater,rating FROM #i', @case_icc, NULL, NULL) k
--This CROSS APPLY UNPIVOTS the tvf columns for formatting
CROSS APPLY(
      SELECT 'r', k.r UNION ALL
      SELECT 'F', k.F UNION ALL
      SELECT 'df1', k.df1 UNION ALL
      SELECT 'df2', k.df2 UNION ALL
      SELECT 'p', k.p UNION ALL
      SELECT 'FL', k.FL UNION ALL
      SELECT 'FU', k.FU UNION ALL
      SELECT 'LB', k.LB UNION ALL
      SELECT 'UB', k.UB
      ) p(stat, value_stat)
 
DROP TABLE #i
This produces the following result.



Copyright 2008-2017 Westclintech LLC         Privacy Policy        Terms of Service