simplify CTE query
<p>I have a data structure where various data elements can be related to one another. One record can be the parent of many child records, but a child record can only have one parent. I'm working on a query to check these record 'families' to see if they are all marked obsolete, such that I can delete them. </p>

<p>To solve this, I wrote a CTE that starts at bottom-most child records, and works up the tree, recording the status, to end up with a 'family' status at the top level record.</p>

<p>One issue I ran into with this approach was when a record has multiple children, I ended up with multiple records for the top level parent. In some cases, one child path could be eligible for deletion, but the other was not. I could not come up with a way to identify this in the CTE, and ended up with a subquery, where I count the number of paths that are delete eligible and are not, then only return records with > 1 'delete' paths and 0 'don't delete' paths.</p>

<p>Here is my code, complete with example data</p>

<pre><code>create table #demand (id int, [status] varchar(10))
create table #relation (parent int, child int)

insert into #demand values (1, 'active')
insert into #demand values (2, 'obsolete')
insert into #demand values (3, 'obsolete')
insert into #demand values (4, 'obsolete')
insert into #demand values (5, 'active')
insert into #demand values (6, 'obsolete')

insert into #relation values(2, 3)
insert into #relation values(2, 4)
insert into #relation values(4, 5)

--the CTE splitFamily traverses the split family records from bottom up, checking that each record is Obsolete. If any record is not
;with
splitFamily as (
    select 
        D.id, 
        DR.child,
        case when D.[status] = 'obsolete' then 'Y' else 'N' end as CanDelete
    from #demand D
    left outer join #relation DR on D.id = DR.parent
    where DR.child is null

    union all

    select
        D.id, 
        DR.child,
        case when D.[status] = 'obsolete' and splitFamily.CanDelete = 'Y' then 'Y' else 'N' end as CanDelete
    from
        splitFamily
    join #relation DR on splitFamily.id = DR.child
    join #demand D  on DR.parent = D.id
)
select id from (
    select parentLevel.id,
    sum(case when parentLevel.CanDelete = 'Y' then 1 else 0 end) as "Y",
    sum(case when parentLevel.CanDelete = 'N' then 1 else 0 end) as "N"
    from splitFamily parentLevel
    --The following join ensures we only return top level parent records to be deleted. 
    left join splitFamily children on parentLevel.id = children.child
    where children.id is null
    group by parentLevel.id
) as splits where Y &gt; 0 and N = 0

drop table #demand
drop table #relation
</code></pre>

<p>This code works, and outputs record 6 as the only eligible delete record. If record 5 is changed to 'obsolete', the query correctly includes 2 in the results. </p>

<p>My question is about whether there is a cleaner, clearer way to identify those split paths within the CTE, so as to avoid the extra subquery and the counting of the paths. To me, this obfuscates the purpose of the code, and make it considerably harder to maintain going forward. </p>