By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();
| version() |
|---|
| 8.0.27 |
CREATE TABLE cas -- French for "case" - case is an SQL keyword and should
( -- not be used for table names!
case_id INTEGER NOT NULL,
cat TEXT NOT NULL,
birth DATE NOT NULL, -- store as a date - only requires 4 bytes
c_type TEXT NULL -- type is a keyword, best avoided
); -- https://www.drupal.org/docs/develop/coding-standards/list-of-sql-reserved-words
INSERT INTO cas (case_id, cat, birth) VALUES
(20033738, 'CASE_OWNER', '1996-04-08'),
(20033738, 'WIFE' , '1995-08-22'),
(20033831, 'CASE_OWNER', '1975-03-05'),
(20033831, 'CHILD' , '2005-03-19'),
(20033831, 'CHILD' , '2006-03-25'),
(20033831, 'CHILD' , '2010-05-20'),
(20033831, 'CHILD' , '2013-10-25'),
(20039301, 'CASE_OWNER', '1999-07-27'),
(20039301, 'WIFE' , '2001-07-05'),
(20039301, 'CHILD' , '2018-10-22'),
(20039334, 'CASE_OWNER', '1994-03-10'),
(30033333, 'CASE_OWNER', '1980-01-01'), -- added a single case owner with one child!
(30033333, 'CHILD' , '2012-09-01'),
(30044444, 'CASE_OWNER', '2015-08-10'), -- added a case owner < 21 yrs of age!
(30055555, 'CASE_OWNER', '1970-02-10'), -- added a couple whose children are all > 21!
(30055555, 'WIFE' , '1972-07-05'),
(30055555, 'CHILD' , '1995-11-22'),
(30055555, 'CHILD' , '1997-05-19'),
(30066666, 'CASE_OWNER', '1970-02-10'), -- added single case owner whose children
(30066666, 'CHILD' , '1989-07-05'), -- are all over 21!
(30066666, 'CHILD' , '1992-11-22'),
(30066666, 'CHILD' , '1994-05-19');
--
-- To check
--
-- SELECT * FROM cas;
--
--
-- 1st query - works only with versions of MySQL >= 8 - uses window functions!
--
--
SELECT
case_id,
cat,
birth,
c_type,
CASE
-- 1st section:
--
-- The logic here is that if the COUNT of case_id is = 1, then there can **only**
-- be a single case_owner and nobdody else is associated with the case!
--
-- Then, there's a choice between the age of the case_owner being greater than
-- or equal to 21 ('SINGLE_PERSON') or less than 21 ('UNKNOWN')
--
WHEN (COUNT(case_id) OVER (PARTITION BY case_id)) = 1
THEN
CASE
WHEN (DATEDIFF(NOW(), birth) / 365.25) >= 21
THEN 'SINGLE_PERSON'
ELSE 'UNKNOWN'
END
-- 2nd section:
| case_id | cat | birth | c_type | c_t |
|---|---|---|---|---|
| 20033738 | CASE_OWNER | 1996-04-08 | null | PAIR_NO_CHILD |
| 20033738 | WIFE | 1995-08-22 | null | PAIR_NO_CHILD |
| 20033831 | CASE_OWNER | 1975-03-05 | null | SINGLE_WITH_CHILD |
| 20033831 | CHILD | 2013-10-25 | null | SINGLE_WITH_CHILD |
| 20033831 | CHILD | 2010-05-20 | null | SINGLE_WITH_CHILD |
| 20033831 | CHILD | 2006-03-25 | null | SINGLE_WITH_CHILD |
| 20033831 | CHILD | 2005-03-19 | null | SINGLE_WITH_CHILD |
| 20039301 | CASE_OWNER | 1999-07-27 | null | PAIR_WITH_CHILD |
| 20039301 | WIFE | 2001-07-05 | null | PAIR_WITH_CHILD |
| 20039301 | CHILD | 2018-10-22 | null | PAIR_WITH_CHILD |
| 20039334 | CASE_OWNER | 1994-03-10 | null | SINGLE_PERSON |
| 30033333 | CASE_OWNER | 1980-01-01 | null | SINGLE_WITH_CHILD |
| 30033333 | CHILD | 2012-09-01 | null | SINGLE_WITH_CHILD |
| 30044444 | CASE_OWNER | 2015-08-10 | null | UNKNOWN |
| 30055555 | CASE_OWNER | 1970-02-10 | null | OTHER |
| 30055555 | WIFE | 1972-07-05 | null | OTHER |
| 30055555 | CHILD | 1997-05-19 | null | OTHER |
| 30055555 | CHILD | 1995-11-22 | null | OTHER |
| 30066666 | CASE_OWNER | 1970-02-10 | null | MULTIPLE |
| 30066666 | CHILD | 1994-05-19 | null | MULTIPLE |
| 30066666 | CHILD | 1992-11-22 | null | MULTIPLE |
| 30066666 | CHILD | 1989-07-05 | null | MULTIPLE |
--
--
-- 2nd query - work with versions of MySQL >= 5.5 - this is the earliest version
-- for which a server is avaiable on db<>fiddle. 5.5 entered EoL 3 years ago!
--
-- Try changing the version of MySQL using the dropdown at the top of this
-- fiddle!
--
SELECT
s.case_id,
s.cat,
s.birth,
--
-- 1st section: these sections correspond to the sections in query 1 above.
--
CASE
WHEN s.c_cnt = 1
THEN
CASE
WHEN s.a_min >= 21
THEN 'SINGLE PERSON'
ELSE 'UNKNOWN'
END
--
-- 2nd section:
--
WHEN s.c_cnt = 2 AND s.w_cnt = 1
THEN 'PAIR_NO_CHILD'
| case_id | cat | birth | c_t |
|---|---|---|---|
| 20033738 | CASE_OWNER | 1996-04-08 | PAIR_NO_CHILD |
| 20033738 | WIFE | 1995-08-22 | PAIR_NO_CHILD |
| 20033831 | CASE_OWNER | 1975-03-05 | SINGLE_WITH_CHILD |
| 20033831 | CHILD | 2013-10-25 | SINGLE_WITH_CHILD |
| 20033831 | CHILD | 2010-05-20 | SINGLE_WITH_CHILD |
| 20033831 | CHILD | 2006-03-25 | SINGLE_WITH_CHILD |
| 20033831 | CHILD | 2005-03-19 | SINGLE_WITH_CHILD |
| 20039301 | CASE_OWNER | 1999-07-27 | PAIR_WITH_CHILD |
| 20039301 | WIFE | 2001-07-05 | PAIR_WITH_CHILD |
| 20039301 | CHILD | 2018-10-22 | PAIR_WITH_CHILD |
| 20039334 | CASE_OWNER | 1994-03-10 | SINGLE PERSON |
| 30033333 | CASE_OWNER | 1980-01-01 | SINGLE_WITH_CHILD |
| 30033333 | CHILD | 2012-09-01 | SINGLE_WITH_CHILD |
| 30044444 | CASE_OWNER | 2015-08-10 | UNKNOWN |
| 30055555 | CASE_OWNER | 1970-02-10 | OTHER |
| 30055555 | WIFE | 1972-07-05 | OTHER |
| 30055555 | CHILD | 1997-05-19 | OTHER |
| 30055555 | CHILD | 1995-11-22 | OTHER |
| 30066666 | CASE_OWNER | 1970-02-10 | MULTIPLE |
| 30066666 | CHILD | 1994-05-19 | MULTIPLE |
| 30066666 | CHILD | 1992-11-22 | MULTIPLE |
| 30066666 | CHILD | 1989-07-05 | MULTIPLE |
--
-- MySQL Performance analysis - feel free to ignore...
--
-- see the brief discussion at the end of the answer!
--
-- The question is here: https://dba.stackexchange.com/questions/305706/mysql-update-column-based-on-multiple-conditions
--
--
-- In order to give every chance to the slower query, I'm performing the windows
-- function query first - any caching should be in favour of the second query, the one
-- which uses a subquery and which works on MySQL >= 5.5
--
--
-- What, exactly, is happening here escapes me! I'm not going to investigate further
-- because (much wasted) time and experience has shown me that MySQL's bugs/
-- performance issues/whatever are not worth pursuing, because it's either
-- documented in an obscure part of the manual or it's a recently introduced
-- bug which has silently been fixed in a few versions down the road or it's
-- always going to be this way.
--
--
-- Suffice to say that the windows function query is ~ 5 - 10 times more performant
-- than the subquery variant - this is because the subquery has to scan the
-- base cas table at least 4 times. That's once for the initial read and then
-- 3 more times for the aggregate functions - COUNT(), SUM() and MIN().
--
-- Tests on my own machine show that this is the case with MySQL Server running
-- locally
--
EXPLAIN ANALYZE -- EXPLAIN ANALYZE only works on MySQL >= 8.0.18
-- https://dev.mysql.com/blog-archive/mysql-explain-analyze/
SELECT
case_id,
cat,
birth,
c_type,
CASE
-- 1st section:
WHEN (COUNT(case_id) OVER (PARTITION BY case_id)) = 1
THEN
CASE
| EXPLAIN |
|---|
| -> Sort: cas.case_id, `(case when (cas.cat = 'CASE_OWNER') then 1 when (cas.cat = 'WIFE') then 2 when (cas.cat = 'CHILD') then 3 end)`, cas.birth DESC (actual time=1.659..1.668 rows=22 loops=1) -> Table scan on <temporary> (cost=2.50..2.50 rows=0) (actual time=0.001..0.006 rows=22 loops=1) -> Temporary table (cost=2.50..2.50 rows=0) (actual time=1.629..1.635 rows=22 loops=1) -> Window aggregate with buffering: min(```((to_days(now()) - to_days(cas.birth)) / 365.25)```) OVER (PARTITION BY cas.case_id ) (actual time=1.374..1.577 rows=22 loops=1) -> Table scan on <temporary> (cost=2.50..2.50 rows=0) (actual time=0.001..0.007 rows=22 loops=1) -> Temporary table (cost=2.50..2.50 rows=0) (actual time=1.328..1.337 rows=22 loops=1) -> Window aggregate with buffering: sum(```(case when (cas.cat = 'WIFE') then 1 else 0 end)```) OVER (PARTITION BY cas.case_id ) (actual time=1.138..1.292 rows=22 loops=1) -> Table scan on <temporary> (cost=2.50..2.50 rows=0) (actual time=0.001..0.007 rows=22 loops=1) -> Temporary table (cost=2.50..2.50 rows=0) (actual time=1.101..1.110 rows=22 loops=1) -> Window aggregate with buffering: count(cas.case_id) OVER (PARTITION BY cas.case_id ) (actual time=0.923..1.075 rows=22 loops=1) -> Table scan on <temporary> (cost=2.50..2.50 rows=0) (actual time=0.000..0.006 rows=22 loops=1) -> Temporary table (cost=2.50..2.50 rows=0) (actual time=0.909..0.916 rows=22 loops=1) -> Window aggregate with buffering: min(```((to_days(now()) - to_days(cas.birth)) / 365.25)```) OVER (PARTITION BY cas.case_id ) (actual time=0.784..0.898 rows=22 loops=1) -> Table scan on <temporary> (cost=2.50..2.50 rows=0) (actual time=0.000..0.005 rows=22 loops=1) -> Temporary table (cost=2.50..2.50 rows=0) (actual time=0.762..0.769 rows=22 loops=1) -> Window aggregate with buffering: sum(```(case when (cas.cat = 'WIFE') then 1 else 0 end)```) OVER (PARTITION BY cas.case_id ) (actual time=0.647..0.751 rows=22 loops=1) -> Table scan on <temporary> (cost=2.50..2.50 rows=0) (actual time=0.000..0.005 rows=22 loops=1) -> Temporary table (cost=2.50..2.50 rows=0) (actual time=0.633..0.640 rows=22 loops=1) -> Window aggregate with buffering: count(cas.case_id) OVER (PARTITION BY cas.case_id ) (actual time=0.524..0.624 rows=22 loops=1) -> Table scan on <temporary> (cost=2.50..2.50 rows=0) (actual time=0.001..0.005 rows=22 loops=1) -> Temporary table (cost=2.50..2.50 rows=0) (actual time=0.511..0.518 rows=22 loops=1) -> Window aggregate with buffering: sum(```(case when (cas.cat = 'WIFE') then 1 else 0 end)```) OVER (PARTITION BY cas.case_id ) (actual time=0.383..0.499 rows=22 loops=1) -> Table scan on <temporary> (cost=2.50..2.50 rows=0) (actual time=0.000..0.005 rows=22 loops=1) -> Temporary table (cost=2.50..2.50 rows=0) (actual time=0.370..0.377 rows=22 loops=1) -> Window aggregate with buffering: count(cas.case_id) OVER (PARTITION BY cas.case_id ) (actual time=0.273..0.361 rows=22 loops=1) -> Table scan on <temporary> (cost=2.50..2.50 rows=0) (actual time=0.001..0.005 rows=22 loops=1) -> Temporary table (cost=2.50..2.50 rows=0) (actual time=0.261..0.266 rows=22 loops=1) -> Window aggregate with buffering: count(cas.case_id) OVER (PARTITION BY cas.case_id ) (actual time=0.085..0.249 rows=22 loops=1) -> Sort: cas.case_id (cost=2.45 rows=22) (actual time=0.046..0.051 rows=22 loops=1) -> Table scan on cas (cost=2.45 rows=22) (actual time=0.012..0.031 rows=22 loops=1) |
EXPLAIN ANALYZE -- we see the cas table is scanned 4 times!
SELECT
s.case_id,
s.cat,
s.birth,
CASE
WHEN s.c_cnt = 1
THEN
CASE
WHEN s.a_min >= 21
THEN 'SINGLE PERSON'
ELSE 'UNKNOWN'
END
WHEN s.c_cnt = 2 AND s.w_cnt = 1
THEN 'PAIR_NO_CHILD'
WHEN s.c_cnt >= 3 AND s.w_cnt = 1
THEN
CASE
WHEN s.a_min < 21
THEN 'PAIR_WITH_CHILD'
ELSE 'OTHER'
END
WHEN s.c_cnt >= 2 AND s.w_cnt = 0
THEN
CASE
WHEN s.a_min < 21
THEN 'SINGLE_WITH_CHILD'
ELSE 'MULTIPLE'
END
| EXPLAIN |
|---|
| -> Table scan on s (cost=0.13..2.77 rows=22) (actual time=0.000..0.003 rows=22 loops=1) -> Materialize (cost=4.78..7.43 rows=22) (actual time=4.470..4.473 rows=22 loops=1) -> Sort: c1.case_id, (case when (c1.cat = 'CASE_OWNER') then 1 when (c1.cat = 'WIFE') then 2 when (c1.cat = 'CHILD') then 3 end), c1.birth DESC (cost=2.45 rows=22) (actual time=0.110..0.134 rows=22 loops=1) -> Table scan on c1 (cost=2.45 rows=22) (actual time=0.012..0.080 rows=22 loops=1) -> Select #3 (subquery in projection; dependent) -> Table scan on <temporary> (actual time=0.000..0.000 rows=1 loops=22) -> Aggregate using temporary table (actual time=0.045..0.046 rows=1 loops=22) -> Filter: (c2.case_id = c1.case_id) (cost=0.47 rows=2) (actual time=0.013..0.026 rows=3 loops=22) -> Table scan on c2 (cost=0.47 rows=22) (actual time=0.006..0.023 rows=22 loops=22) -> Select #4 (subquery in projection; dependent) -> Table scan on <temporary> (actual time=0.000..0.000 rows=1 loops=22) -> Aggregate using temporary table (actual time=0.049..0.049 rows=1 loops=22) -> Filter: (c3.case_id = c1.case_id) (cost=0.47 rows=2) (actual time=0.014..0.027 rows=3 loops=22) -> Table scan on c3 (cost=0.47 rows=22) (actual time=0.007..0.024 rows=22 loops=22) -> Select #5 (subquery in projection; dependent) -> Table scan on <temporary> (actual time=0.000..0.000 rows=1 loops=22) -> Aggregate using temporary table (actual time=0.056..0.056 rows=1 loops=22) -> Filter: (c4.case_id = c1.case_id) (cost=0.47 rows=2) (actual time=0.013..0.026 rows=3 loops=22) -> Table scan on c4 (cost=0.47 rows=22) (actual time=0.006..0.023 rows=22 loops=22) |
EXPLAIN ANALYZE
SELECT
case_id,
cat,
birth,
c_type,
CASE
-- 1st section:
WHEN (COUNT(case_id) OVER (PARTITION BY case_id)) = 1
THEN
CASE
WHEN (DATEDIFF(NOW(), birth) / 365.25) >= 21
THEN 'SINGLE_PERSON'
ELSE 'UNKNOWN'
END
-- 2nd section:
WHEN COUNT(case_id) OVER (PARTITION BY case_id) = 2
AND SUM(CASE WHEN cat = 'WIFE' THEN 1 ELSE 0 END) OVER (PARTITION BY case_id) = 1
THEN 'PAIR_NO_CHILD'
-- 3rd section:
WHEN COUNT(case_id) OVER (PARTITION BY case_id) >= 3
AND SUM(CASE WHEN cat = 'WIFE' THEN 1 ELSE 0 END) OVER (PARTITION BY case_id) = 1
THEN
CASE
| EXPLAIN |
|---|
| -> Window aggregate with buffering: min(```((to_days(now()) - to_days(cas.birth)) / 365.25)```) OVER (PARTITION BY cas.case_id ) (actual time=1.420..1.596 rows=22 loops=1) -> Table scan on <temporary> (cost=2.50..2.50 rows=0) (actual time=0.001..0.006 rows=22 loops=1) -> Temporary table (cost=2.50..2.50 rows=0) (actual time=1.378..1.386 rows=22 loops=1) -> Window aggregate with buffering: sum(```(case when (cas.cat = 'WIFE') then 1 else 0 end)```) OVER (PARTITION BY cas.case_id ) (actual time=1.199..1.339 rows=22 loops=1) -> Table scan on <temporary> (cost=2.50..2.50 rows=0) (actual time=0.001..0.007 rows=22 loops=1) -> Temporary table (cost=2.50..2.50 rows=0) (actual time=1.141..1.150 rows=22 loops=1) -> Window aggregate with buffering: count(cas.case_id) OVER (PARTITION BY cas.case_id ) (actual time=1.008..1.126 rows=22 loops=1) -> Table scan on <temporary> (cost=2.50..2.50 rows=0) (actual time=0.001..0.006 rows=22 loops=1) -> Temporary table (cost=2.50..2.50 rows=0) (actual time=0.981..0.988 rows=22 loops=1) -> Window aggregate with buffering: min(```((to_days(now()) - to_days(cas.birth)) / 365.25)```) OVER (PARTITION BY cas.case_id ) (actual time=0.851..0.965 rows=22 loops=1) -> Table scan on <temporary> (cost=2.50..2.50 rows=0) (actual time=0.001..0.006 rows=22 loops=1) -> Temporary table (cost=2.50..2.50 rows=0) (actual time=0.827..0.835 rows=22 loops=1) -> Window aggregate with buffering: sum(```(case when (cas.cat = 'WIFE') then 1 else 0 end)```) OVER (PARTITION BY cas.case_id ) (actual time=0.708..0.812 rows=22 loops=1) -> Table scan on <temporary> (cost=2.50..2.50 rows=0) (actual time=0.000..0.005 rows=22 loops=1) -> Temporary table (cost=2.50..2.50 rows=0) (actual time=0.687..0.694 rows=22 loops=1) -> Window aggregate with buffering: count(cas.case_id) OVER (PARTITION BY cas.case_id ) (actual time=0.573..0.672 rows=22 loops=1) -> Table scan on <temporary> (cost=2.50..2.50 rows=0) (actual time=0.001..0.005 rows=22 loops=1) -> Temporary table (cost=2.50..2.50 rows=0) (actual time=0.551..0.559 rows=22 loops=1) -> Window aggregate with buffering: sum(```(case when (cas.cat = 'WIFE') then 1 else 0 end)```) OVER (PARTITION BY cas.case_id ) (actual time=0.444..0.538 rows=22 loops=1) -> Table scan on <temporary> (cost=2.50..2.50 rows=0) (actual time=0.001..0.005 rows=22 loops=1) -> Temporary table (cost=2.50..2.50 rows=0) (actual time=0.422..0.429 rows=22 loops=1) -> Window aggregate with buffering: count(cas.case_id) OVER (PARTITION BY cas.case_id ) (actual time=0.312..0.401 rows=22 loops=1) -> Table scan on <temporary> (cost=2.50..2.50 rows=0) (actual time=0.001..0.005 rows=22 loops=1) -> Temporary table (cost=2.50..2.50 rows=0) (actual time=0.286..0.293 rows=22 loops=1) -> Window aggregate with buffering: count(cas.case_id) OVER (PARTITION BY cas.case_id ) (actual time=0.080..0.274 rows=22 loops=1) -> Sort: cas.case_id (cost=2.45 rows=22) (actual time=0.045..0.051 rows=22 loops=1) -> Table scan on cas (cost=2.45 rows=22) (actual time=0.012..0.030 rows=22 loops=1) |
EXPLAIN ANALYZE
SELECT
s.case_id,
s.cat,
s.birth,
CASE
WHEN s.c_cnt = 1
THEN
CASE
WHEN s.a_min >= 21
THEN 'SINGLE PERSON'
ELSE 'UNKNOWN'
END
WHEN s.c_cnt = 2 AND s.w_cnt = 1
THEN 'PAIR_NO_CHILD'
WHEN s.c_cnt >= 3 AND s.w_cnt = 1
THEN
CASE
WHEN s.a_min < 21
THEN 'PAIR_WITH_CHILD'
ELSE 'OTHER'
END
WHEN s.c_cnt >= 2 AND s.w_cnt = 0
THEN
CASE
WHEN s.a_min < 21
THEN 'SINGLE_WITH_CHILD'
ELSE 'MULTIPLE'
END
| EXPLAIN |
|---|
| -> Table scan on s (cost=0.13..2.77 rows=22) (actual time=0.001..0.003 rows=22 loops=1) -> Materialize (cost=4.78..7.43 rows=22) (actual time=4.827..4.831 rows=22 loops=1) -> Table scan on c1 (cost=2.45 rows=22) (actual time=0.011..0.044 rows=22 loops=1) -> Select #3 (subquery in projection; dependent) -> Table scan on <temporary> (actual time=0.000..0.000 rows=1 loops=22) -> Aggregate using temporary table (actual time=0.045..0.045 rows=1 loops=22) -> Filter: (c2.case_id = c1.case_id) (cost=0.47 rows=2) (actual time=0.013..0.025 rows=3 loops=22) -> Table scan on c2 (cost=0.47 rows=22) (actual time=0.006..0.022 rows=22 loops=22) -> Select #4 (subquery in projection; dependent) -> Table scan on <temporary> (actual time=0.000..0.000 rows=1 loops=22) -> Aggregate using temporary table (actual time=0.050..0.050 rows=1 loops=22) -> Filter: (c3.case_id = c1.case_id) (cost=0.47 rows=2) (actual time=0.013..0.027 rows=3 loops=22) -> Table scan on c3 (cost=0.47 rows=22) (actual time=0.006..0.023 rows=22 loops=22) -> Select #5 (subquery in projection; dependent) -> Table scan on <temporary> (actual time=0.000..0.000 rows=1 loops=22) -> Aggregate using temporary table (actual time=0.075..0.075 rows=1 loops=22) -> Filter: (c4.case_id = c1.case_id) (cost=0.47 rows=2) (actual time=0.033..0.046 rows=3 loops=22) -> Table scan on c4 (cost=0.47 rows=22) (actual time=0.025..0.043 rows=22 loops=22) |
--
-- Rabbit hole! Hic sunt leones!
--
--
-- You might want to look at the ORDER BY in the LEAD() function - another
-- window function! Discarded, but maybe one to keep in mind?
--
SELECT
case_id,
cat,
birth,
c_type,
--
-- Note the use of the LEAD() window function - eventually discarded in this case
-- but can be very useful in other scenarios!
--
CASE
WHEN (COUNT(case_id) OVER (PARTITION BY case_id)) = 2 THEN
CASE
WHEN (cat IN ('CASE_OWNER'))
AND
LEAD(cat)
OVER (PARTITION BY case_id
ORDER BY
CASE
WHEN cat = 'CASE_OWNER' THEN 1
WHEN cat = 'WIFE' THEN 2
WHEN cat = 'CHILD' THEN 3
END
| case_id | cat | birth | c_type | c_t |
|---|---|---|---|---|
| 20033738 | CASE_OWNER | 1996-04-08 | null | PAIR_NO_CHILD |
| 20033738 | WIFE | 1995-08-22 | null | PAIR_NO_CHILD |
| 20033831 | CASE_OWNER | 1975-03-05 | null | YYYY |
| 20033831 | CHILD | 2013-10-25 | null | YYYY |
| 20033831 | CHILD | 2010-05-20 | null | YYYY |
| 20033831 | CHILD | 2006-03-25 | null | YYYY |
| 20033831 | CHILD | 2005-03-19 | null | YYYY |
| 20039301 | CASE_OWNER | 1999-07-27 | null | YYYY |
| 20039301 | WIFE | 2001-07-05 | null | YYYY |
| 20039301 | CHILD | 2018-10-22 | null | YYYY |
| 20039334 | CASE_OWNER | 1994-03-10 | null | YYYY |
| 30033333 | CASE_OWNER | 1980-01-01 | null | null |
| 30033333 | CHILD | 2012-09-01 | null | null |
| 30044444 | CASE_OWNER | 2015-08-10 | null | YYYY |
| 30055555 | CASE_OWNER | 1970-02-10 | null | YYYY |
| 30055555 | WIFE | 1972-07-05 | null | YYYY |
| 30055555 | CHILD | 1997-05-19 | null | YYYY |
| 30055555 | CHILD | 1995-11-22 | null | YYYY |
| 30066666 | CASE_OWNER | 1970-02-10 | null | YYYY |
| 30066666 | CHILD | 1994-05-19 | null | YYYY |
| 30066666 | CHILD | 1992-11-22 | null | YYYY |
| 30066666 | CHILD | 1989-07-05 | null | YYYY |