base.episode_lf_entries

[oieeri26]

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.

[gregorp]

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.

[oieeri26]

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