Open oieeri26 opened 9 years ago
It'd be nice to supplement this with a query to illustrate the problem... 2 months later I'm having trouble remembering exactly what this is.
In the base.episode_lf_entries table we have roughly 100 ids that suggest that the child had multiple adoptions, here's the query to find the duplicates.
SELECT
id.id_prsn_child
,e.discharge_type
,e.state_fiscal_yyyy
FROM
(SELECT
id_prsn_child
,COUNT(id_prsn_child) AS count
FROM base.episode_lf_entries
GROUP BY id_prsn_child
HAVING COUNT(id_prsn_child) > 1) AS id
LEFT JOIN base.episode_lf_entries AS e
ON id.id_prsn_child = e.id_prsn_child
WHERE discharge_type = 'adoption'
ORDER BY id.id_prsn_child
When I checked the ids against base.rptPlacement_Events, using the query below, it appears that most of these children have not had multiple adoptions.
SELECT
*
FROM
(SELECT
id_prsn_child
,COUNT(id_prsn_child) AS count
FROM base.episode_lf_entries
GROUP BY id_prsn_child
HAVING COUNT(id_prsn_child) > 1) AS id
LEFT JOIN base.rptPlacement_Events AS pe
ON id.id_prsn_child = pe.child
WHERE tx_end_rsn = 'adoption'
ORDER BY pe.child
There are around 100 child ids that have two records. When comparing the records base.rptPlacement and base.rptPlacement_Events it appeared that the ids we compared only had one adoption, not two.