PacUqB6m

1CREATE TABLE IF NOT EXISTS development.kra_ref_threshold
2(
3  grp_id TINYINT UNSIGNED NOT NULL PRIMARY KEY,
4  hi_val TINYINT UNSIGNED NOT NULL,
5  hi_oper CHAR(2) NOT NULL,
6  lo_oper CHAR(2) NOT NULL,
7  lo_val TINYINT UNSIGNED NOT NULL
8)COMMENT='Reference for limits for delta months group buckets; column order is intentional';
9
10INSERT INTO development.kra_ref_threshold
11(grp_id, hi_val, hi_oper, lo_oper, lo_val)
12VALUES
13(1, 11, '<=', '>=', 0),
14(2, 23, '<=', '>=', 12),
15(3, 35, '<=', '>=', 24);
16
17# Values across hi and lo cols must be unique
18#  - div by nrow should return 2
19#  - div by ncol (2 cols, hi and lo) should return 3
20# These should both return 1
21SELECT
22   (COUNT(DISTINCT(val))/(SELECT COUNT(*) FROM kra_ref_threshold)) = 2 AS check_div_by_nrow,
23   (COUNT(DISTINCT(val))/2) = 3 AS check_div_by_ncol
24FROM
25(
26SELECT hi_val AS val FROM kra_ref_threshold
27UNION ALL
28SELECT lo_val AS val FROM kra_ref_threshold
29) AS tmp;
30
31SELECT
32  COUNT(DISTINCT(diff)) = 1 AS check_eq_diff
33FROM
34(
35SELECT
36  grp_id,
37  hi_val - lo_val AS diff
38FROM kra_ref_threshold
39GROUP BY grp_id
40) AS tmp;