SQL Server KAPPA_FLEISS_TV Function
Updated 2023-11-06 14:59:27.660000
Description
Use the table-valued function KAPPA_FLEISS_TV to calculate Fleiss' kappa (κ) as an index of inter-rater agreement between m raters on categorical data. KAPPA_FLEISS_TV also calculates the category-wise kappa and the corresponding test statistics. The equation for Fleiss' kappa is:
\kappa = \frac{P_i - P_j}{1-P_j}
See the example below for an explanation of how Pi and Pj are calculated.
Syntax
SELECT * FROM [westclintech].[wct].[KAPPA_FLEISS_TV](
<@InputData_RangeQuery, nvarchar(max),>)
Arguments
@InputData_RangeQuery
a T-SQL statement, as a string, that specifies the subject, rater and rating values.
Return Type
table
| colName | colDatatype | colDesc |
|---|---|---|
| Rating | sql_variant | the user supplied rating |
| Z | float | the test statistic |
| K | float | the kappa value |
| P | float | the p-value |
| SE | float | the standard error |
Remarks
The function is insensitive to order; it does not matter what order the ratings are passed in.
Subjects, raters and ratings are automatically combined by the function.
Examples
SELECT *
INTO #k
FROM
(
SELECT ' Subject 01',
'A',
5
UNION ALL
SELECT ' Subject 01',
'B',
5
UNION ALL
SELECT ' Subject 01',
'C',
5
UNION ALL
SELECT ' Subject 01',
'D',
5
UNION ALL
SELECT ' Subject 01',
'E',
5
UNION ALL
SELECT ' Subject 01',
'F',
5
UNION ALL
SELECT ' Subject 01',
'G',
5
UNION ALL
SELECT ' Subject 01',
'H',
5
UNION ALL
SELECT ' Subject 01',
'I',
5
UNION ALL
SELECT ' Subject 01',
'J',
5
UNION ALL
SELECT ' Subject 01',
'K',
5
UNION ALL
SELECT ' Subject 01',
'L',
5
UNION ALL
SELECT ' Subject 01',
'M',
5
UNION ALL
SELECT ' Subject 01',
'N',
5
UNION ALL
SELECT ' Subject 02',
'A',
2
UNION ALL
SELECT ' Subject 02',
'B',
2
UNION ALL
SELECT ' Subject 02',
'C',
3
UNION ALL
SELECT ' Subject 02',
'D',
3
UNION ALL
SELECT ' Subject 02',
'E',
3
UNION ALL
SELECT ' Subject 02',
'F',
3
UNION ALL
SELECT ' Subject 02',
'G',
3
UNION ALL
SELECT ' Subject 02',
'H',
3
UNION ALL
SELECT ' Subject 02',
'I',
4
UNION ALL
SELECT ' Subject 02',
'J',
4
UNION ALL
SELECT ' Subject 02',
'K',
4
UNION ALL
SELECT ' Subject 02',
'L',
4
UNION ALL
SELECT ' Subject 02',
'M',
5
UNION ALL
SELECT ' Subject 02',
'N',
5
UNION ALL
SELECT ' Subject 03',
'A',
3
UNION ALL
SELECT ' Subject 03',
'B',
3
UNION ALL
SELECT ' Subject 03',
'C',
3
UNION ALL
SELECT ' Subject 03',
'D',
4
UNION ALL
SELECT ' Subject 03',
'E',
4
UNION ALL
SELECT ' Subject 03',
'F',
4
UNION ALL
SELECT ' Subject 03',
'G',
4
UNION ALL
SELECT ' Subject 03',
'H',
4
UNION ALL
SELECT ' Subject 03',
'I',
5
UNION ALL
SELECT ' Subject 03',
'J',
5
UNION ALL
SELECT ' Subject 03',
'K',
5
UNION ALL
SELECT ' Subject 03',
'L',
5
UNION ALL
SELECT ' Subject 03',
'M',
5
UNION ALL
SELECT ' Subject 03',
'N',
5
UNION ALL
SELECT ' Subject 04',
'A',
2
UNION ALL
SELECT ' Subject 04',
'B',
2
UNION ALL
SELECT ' Subject 04',
'C',
2
UNION ALL
SELECT ' Subject 04',
'D',
3
UNION ALL
SELECT ' Subject 04',
'E',
3
UNION ALL
SELECT ' Subject 04',
'F',
3
UNION ALL
SELECT ' Subject 04',
'G',
3
UNION ALL
SELECT ' Subject 04',
'H',
3
UNION ALL
SELECT ' Subject 04',
'I',
3
UNION ALL
SELECT ' Subject 04',
'J',
3
UNION ALL
SELECT ' Subject 04',
'K',
3
UNION ALL
SELECT ' Subject 04',
'L',
3
UNION ALL
SELECT ' Subject 04',
'M',
4
UNION ALL
SELECT ' Subject 04',
'N',
4
UNION ALL
SELECT ' Subject 05',
'A',
1
UNION ALL
SELECT ' Subject 05',
'B',
1
UNION ALL
SELECT ' Subject 05',
'C',
2
UNION ALL
SELECT ' Subject 05',
'D',
2
UNION ALL
SELECT ' Subject 05',
'E',
3
UNION ALL
SELECT ' Subject 05',
'F',
3
UNION ALL
SELECT ' Subject 05',
'G',
3
UNION ALL
SELECT ' Subject 05',
'H',
3
UNION ALL
SELECT ' Subject 05',
'I',
3
UNION ALL
SELECT ' Subject 05',
'J',
3
UNION ALL
SELECT ' Subject 05',
'K',
3
UNION ALL
SELECT ' Subject 05',
'L',
3
UNION ALL
SELECT ' Subject 05',
'M',
4
UNION ALL
SELECT ' Subject 05',
'N',
5
UNION ALL
SELECT ' Subject 06',
'A',
1
UNION ALL
SELECT ' Subject 06',
'B',
1
UNION ALL
SELECT ' Subject 06',
'C',
1
UNION ALL
SELECT ' Subject 06',
'D',
1
UNION ALL
SELECT ' Subject 06',
'E',
1
UNION ALL
SELECT ' Subject 06',
'F',
1
UNION ALL
SELECT ' Subject 06',
'G',
1
UNION ALL
SELECT ' Subject 06',
'H',
2
UNION ALL
SELECT ' Subject 06',
'I',
2
UNION ALL
SELECT ' Subject 06',
'J',
2
UNION ALL
SELECT ' Subject 06',
'K',
2
UNION ALL
SELECT ' Subject 06',
'L',
2
UNION ALL
SELECT ' Subject 06',
'M',
2
UNION ALL
SELECT ' Subject 06',
'N',
2
UNION ALL
SELECT ' Subject 07',
'A',
1
UNION ALL
SELECT ' Subject 07',
'B',
1
UNION ALL
SELECT ' Subject 07',
'C',
1
UNION ALL
SELECT ' Subject 07',
'D',
2
UNION ALL
SELECT ' Subject 07',
'E',
2
UNION ALL
SELECT ' Subject 07',
'F',
3
UNION ALL
SELECT ' Subject 07',
'G',
3
UNION ALL
SELECT ' Subject 07',
'H',
3
UNION ALL
SELECT ' Subject 07',
'I',
3
UNION ALL
SELECT ' Subject 07',
'J',
3
UNION ALL
SELECT ' Subject 07',
'K',
3
UNION ALL
SELECT ' Subject 07',
'L',
4
UNION ALL
SELECT ' Subject 07',
'M',
4
UNION ALL
SELECT ' Subject 07',
'N',
4
UNION ALL
SELECT ' Subject 08',
'A',
1
UNION ALL
SELECT ' Subject 08',
'B',
1
UNION ALL
SELECT ' Subject 08',
'C',
2
UNION ALL
SELECT ' Subject 08',
'D',
2
UNION ALL
SELECT ' Subject 08',
'E',
2
UNION ALL
SELECT ' Subject 08',
'F',
2
UNION ALL
SELECT ' Subject 08',
'G',
2
UNION ALL
SELECT ' Subject 08',
'H',
3
UNION ALL
SELECT ' Subject 08',
'I',
3
UNION ALL
SELECT ' Subject 08',
'J',
3
UNION ALL
SELECT ' Subject 08',
'K',
4
UNION ALL
SELECT ' Subject 08',
'L',
4
UNION ALL
SELECT ' Subject 08',
'M',
5
UNION ALL
SELECT ' Subject 08',
'N',
5
UNION ALL
SELECT ' Subject 09',
'A',
1
UNION ALL
SELECT ' Subject 09',
'B',
1
UNION ALL
SELECT ' Subject 09',
'C',
1
UNION ALL
SELECT ' Subject 09',
'D',
1
UNION ALL
SELECT ' Subject 09',
'E',
1
UNION ALL
SELECT ' Subject 09',
'F',
1
UNION ALL
SELECT ' Subject 09',
'G',
2
UNION ALL
SELECT ' Subject 09',
'H',
2
UNION ALL
SELECT ' Subject 09',
'I',
2
UNION ALL
SELECT ' Subject 09',
'J',
2
UNION ALL
SELECT ' Subject 09',
'K',
2
UNION ALL
SELECT ' Subject 09',
'L',
3
UNION ALL
SELECT ' Subject 09',
'M',
3
UNION ALL
SELECT ' Subject 09',
'N',
4
UNION ALL
SELECT ' Subject 10',
'A',
2
UNION ALL
SELECT ' Subject 10',
'B',
2
UNION ALL
SELECT ' Subject 10',
'C',
3
UNION ALL
SELECT ' Subject 10',
'D',
3
UNION ALL
SELECT ' Subject 10',
'E',
4
UNION ALL
SELECT ' Subject 10',
'F',
4
UNION ALL
SELECT ' Subject 10',
'G',
4
UNION ALL
SELECT ' Subject 10',
'H',
5
UNION ALL
SELECT ' Subject 10',
'I',
5
UNION ALL
SELECT ' Subject 10',
'J',
5
UNION ALL
SELECT ' Subject 10',
'K',
5
UNION ALL
SELECT ' Subject 10',
'L',
5
UNION ALL
SELECT ' Subject 10',
'M',
5
UNION ALL
SELECT ' Subject 10',
'N',
5
) n(subject, rater, rating);
SELECT *
FROM wct.KAPPA_FLEISS_TV('SELECT subject, rater, rating FROM #k')
ORDER BY rating;
This produces the following result.
| Rating | Z | K | P | SE |
|---|---|---|---|---|
| NULL | 12.3742910591905 | 0.209930704421955 | 3.60059432346663E-35 | 0.0169650692243931 |
| 1 | 6.07191587500703 | 0.201282051282051 | 1.26393144622578E-09 | 0.0331496772065898 |
| 2 | 2.40335159747776 | 0.0796703296703298 | 0.0162455593601469 | 0.0331496772065898 |
| 3 | 5.17644959456748 | 0.171597633136095 | 2.26148113389763E-07 | 0.0331496772065898 |
| 4 | 0.9164910757116 | 0.0303813833225598 | 0.359409386074102 | 0.0331496772065898 |
| 5 | 15.3140765743498 | 0.507656695156695 | 6.15802423732574E-53 | 0.0331496772065898 |